Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I am new to click view and I am a SAS programmer. I am trying to replicate this "do until" in Click view to create a field renewal_date
The else if part and I am struggling with as I want the field to add 1 year until it is greater than or equal to the pv_edate field.
note this is SAS code; Intnx is like ADDYEAR.
Renewal_Date=intnx('year',pol_startDate,1,'sameday');
If pv_stat=2 then do;
Renewal_Date=Renewal_DAte
end;
else if pv_stat in (3,4,5,6) then do;
do until (Renewal_Date>=pv_edate);
Renewal_Date=intnx('year',Renewal_Date,1,'sameday');
end;
for example:
Renewal_Date for Row 1 of data is set as 25MAR2011
PV_EDATE is Set too 05JUL2013
The result will mean that after the do until the Renewal_date will change too 22MAR2014.
Thanks for this String it seems logical, I have wriiten this statement based on your suggestion. Although I not familar with the DayNumberOfYear function. Is the number of days in the calander year?
LOAD
IF(PolicyVersionStatusId=2,TempRenewalDate,
Addyears(TempRenewalDate, if(DayNumberOfYear(TempRenewalDate)>DayNumberOfYear( PolicyVersionEndDate),0,1) + year( PolicyVersionEndDate)-year(TempRenewalDate))) as RenewalDate,
LOAD
ADDYEARS (date(num(floor(InitialStartDate)),'dd/MM/yyyy'),1) asTempRenewalDate
,
In this example however the RenewalDate is coming out as 11/01/2014 rather than 11/01/2013
PolicyStartDate | PolicyVersionStartDate | PolicyVersionEndDate | PolicyEndDate | RenewalDate |
11/01/2011 | 02/01/2014 | 11/01/2013 | 11/01/2013 | 11/01/2014 |
I think the string needs modifying as it doesnt take care of the -- do until greater "or equal part".
I have added an = part as such
(DayNumberOfYear(TempRenewalDate)>=DayNumberOfYear( PolicyVersionEndDate),0,1)
but I am not sure is this is correct or not. It resolves this particular record.
I am however finding a lot of differences in terms of the resulting renewal date from my orignal code to that of the formula in qlikview.
Maybe something like this:
LOAD
FieldA, FieldB, ...., FieldX,
addyears(Renewal_Date, if(DayNumberOfYear(Renewal_Date)>DayNumberOfYear(PV_EDATE),0,1) + year(PV_EDATE)-year(Renewal_Date)) as Renewal_Date
FROM MySource;
If the dates in your source are string values you'll have to use the date# function to turn them into real dates: date#(Renewal_Date,'DDMMMYYYY')
Thanks for this String it seems logical, I have wriiten this statement based on your suggestion. Although I not familar with the DayNumberOfYear function. Is the number of days in the calander year?
LOAD
IF(PolicyVersionStatusId=2,TempRenewalDate,
Addyears(TempRenewalDate, if(DayNumberOfYear(TempRenewalDate)>DayNumberOfYear( PolicyVersionEndDate),0,1) + year( PolicyVersionEndDate)-year(TempRenewalDate))) as RenewalDate,
LOAD
ADDYEARS (date(num(floor(InitialStartDate)),'dd/MM/yyyy'),1) asTempRenewalDate
,
In this example however the RenewalDate is coming out as 11/01/2014 rather than 11/01/2013
PolicyStartDate | PolicyVersionStartDate | PolicyVersionEndDate | PolicyEndDate | RenewalDate |
11/01/2011 | 02/01/2014 | 11/01/2013 | 11/01/2013 | 11/01/2014 |
I think the string needs modifying as it doesnt take care of the -- do until greater "or equal part".
I have added an = part as such
(DayNumberOfYear(TempRenewalDate)>=DayNumberOfYear( PolicyVersionEndDate),0,1)
but I am not sure is this is correct or not. It resolves this particular record.
I am however finding a lot of differences in terms of the resulting renewal date from my orignal code to that of the formula in qlikview.
In My SAS code I perform another step that actually then creates the same result as my previous comment so this statement with the = sign would be correct