topic Re: do until to create a field in my data in Water Cooler

do until to create a field in my data

Wed, 22 Jan 2014 17:21:57 GMT

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.

Re: do until to create a field in my data

Gysbert_Wassenaar — Wed, 22 Jan 2014 17:36:06 GMT

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')

Re: do until to create a field in my data

Thu, 23 Jan 2014 10:56:44 GMT

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/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.

Re: do until to create a field in my data

Thu, 23 Jan 2014 12:51:06 GMT

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