hello,
This is the data set "have".
data have ;
informat year_month yymmn6.;
format year_month date9.;
input year_month monthNum amount ;
datalines;
201511 11 110
201512 12 112
201601 1 101
201602 2 102
201603 3 103
201604 4 104
201605 5 105
201606 6 106
201607 7 107
201608 8 108
201609 9 109
201610 10 110
201611 11 111
201612 12 112
201701 1 101
;
run ;
And what I would like is to cumulate the amount like this :
--> for month n° 1, cumulate the last 2 month
--> for month n° 2, cumulate the last 3 month
--> for month n° 3, cumulate the last 4 month
...
...
--> for month n° 12, cumulate the last 13 month
You need to set a condition or a starting point, in your case a (re-)starting point:
data want;
set have;
retain cum_amount 0;
cum_amount + amount;
output;
if month(year_month) = 12 then cum_amount = amount;
run;
data want;
set have;
retain cum_amount 0;
cum_amount + amount;
run;
Depending on where you want to start, you have to set a condition for the increment statement.
Thanks a lot Kurt but I do not succeed to get the attended result.
example, for jan2016 , the cumul is all values having month <= jan2016 instead of jan+dec only.
You need to set a condition or a starting point, in your case a (re-)starting point:
data want;
set have;
retain cum_amount 0;
cum_amount + amount;
output;
if month(year_month) = 12 then cum_amount = amount;
run;
Something like:
data have; informat year_month yymmn6.; format year_month date9.; input year_month monthNum amount; yr=year(year_month); datalines; 201511 11 110 201512 12 112 201601 1 101 201602 2 102 201603 3 103 201604 4 104 201605 5 105 201606 6 106 201607 7 107 201608 8 108 201609 9 109 201610 10 110 201611 11 111 201612 12 112 201701 1 101 ; run; proc sort data=have; by yr monthnum; run; data want; set have; retain cumulate; by yr; cumulate=ifn(first.yr,amount,cumulate+amount); run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.