topic Re: do until in SAS Procedures

do until

Tal — Tue, 05 May 2020 18:14:47 GMT

hello,

trying to populate all next payment dates till end of May for each billing mode+last billing date record

4-monthly

7-weekly

8-bi weekly

and i dont care about the records with missing last billing date. Can be removed

have:
billing mode last Billing date
---------------------------------
4 10-mar-2020
7 01-may-2020
7 15-may-2020
7 .
8 20-apr-2020
8 .

want:
billing mode last Billing date next payment date
---------------------------------------------------
4 10-mar-2020 10-apr-2020
4 10-mar-2020 10-may-2020
7 01-may-2020 08-may-2020
7 01-may-2020 15-may-2020
7 01-may-2020 22-may-2020
7 01-may-2020 29-may-2020
7 15-may-2020 22-may-2020
7 15-may-2020 29-may-2020
7 . .
8 20-apr-2020 04-may-2020
8 20-apr-2020 18-may-2020
8 . .

i am using this below but it is not right. Looks like i need to use grouping by along with the loop but i am out of ideas how . Any suggestions pls? thanks

data want;
next_payment_dte=Last_Billing_Date;
do until (next_payment_dte<='31May2020'd);
set have;
if billing_mode=4 then next_payment_dte=intnx('month',next_payment_dte,1,"sameday");
else if billing_mode=8 then next_payment_dte=intnx('day',next_payment_dte,14,"sameday");
else if billing_mode=7 then next_payment_dte=intnx('day',next_payment_dte,7,"sameday");
end;
run;

Re: do until

Reeza — Tue, 05 May 2020 19:12:29 GMT

do until (next_payment_dte>='31May2020'd);

I think you need greater than for starters, not less than for your condition. Note that for a DO UNTIL loop it will always evaluate at least once. Note that you do not have an output statement so if it loops more than once you won't have any billing dates generated except the last. You likely want to add an OUTPUT statement within the loop.

Re: do until

biopharma — Tue, 05 May 2020 21:06:17 GMT

You are using a specialized do loop with a set statement inside the do loop. It can be done but you are not doing that correctly. You will have to understand the mechanism of the natural looping of the data step before moving on to do advance do loops.

There are also many different Shift index values. MONTH, WEEK and WEEK2 are the ones you need. Try the below and see if that works for you.

data want ;
   set your_input_dataset ;
   _dte = last_billing_date ;
   format _dte next_payment_dte date9. ;
   if last_billing_date>. then 
   do until(next_payment_dte>="31May2020"d) ;
      if billing_mode=4 then next_payment_dte=intnx('month',_dte,1,"sameday");
      else if billing_mode=8 then next_payment_dte=intnx('week2',_dte,1,"sameday");
      else if billing_mode=7 then next_payment_dte=intnx('week',_dte,1,"sameday");
      _dte = next_payment_dte ;
      if next_payment_dte<="31May2020"d then output ;
   end ;
   if last_billing_date = . then output ;
run ;

Re: do until

Tal — Tue, 05 May 2020 23:20:41 GMT

i initially used do while and then changed to do until but forgot to change the condition

Thanks for your input.

Re: do until

Tal — Tue, 05 May 2020 23:21:45 GMT

yea this worked

million thanks to both