cumulating the n previous rows depending the number of the month

[Nasser_DRMCP]

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

[Kurt_Bremser]

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.

[Nasser_DRMCP]

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.

[Kurt_Bremser · Accepted solution]

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;

[RW9]

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;