3 Replies Latest reply: Jan 23, 2014 7:51 AM by Rodney Arbon RSS

    do until to create a field in my data

      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

          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

              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.