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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.