## cumulating the n previous rows depending the number of the month

Solved
Frequent Contributor
Posts: 117

# cumulating the n previous rows depending the number of the month

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

Accepted Solutions
Solution
‎10-30-2017 11:19 AM
Super User
Posts: 9,877

## Re: cumulating the n previous rows depending the number of the 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;``````

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Super User
Posts: 9,877

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

``````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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 117

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

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.

Solution
‎10-30-2017 11:19 AM
Super User
Posts: 9,877

## Re: cumulating the n previous rows depending the number of the 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;``````

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 9,402

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

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;

```
☑ This topic is solved.