topic Re: cumulating the n previous rows depending the number of the month in SAS Programming

cumulating the n previous rows depending the number of the month

Nasser_DRMCP — Mon, 30 Oct 2017 14:27:03 GMT

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

Re: cumulating the n previous rows depending the number of the month

Kurt_Bremser — Mon, 30 Oct 2017 14:32:05 GMT

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.

Re: cumulating the n previous rows depending the number of the month

RW9 — Mon, 30 Oct 2017 14:35:21 GMT

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;

Re: cumulating the n previous rows depending the number of the month

Nasser_DRMCP — Mon, 30 Oct 2017 15:02:39 GMT

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.

Re: cumulating the n previous rows depending the number of the month

Kurt_Bremser — Mon, 30 Oct 2017 15:10:00 GMT

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;