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;
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 ;
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.
i initially used do while and then changed to do until but forgot to change the condition
Thanks for your input.
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 ;
yea this worked
million thanks to both
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: