DATA Step, Macro, Functions and more

cumulating the n previous rows depending the number of the month

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 117
Accepted Solution

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

Posted in reply to Nasser_alfea

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

View solution in original post


All Replies
Super User
Posts: 9,877

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

Posted in reply to Nasser_alfea
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

Posted in reply to KurtBremser

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

Posted in reply to Nasser_alfea

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
Super User
Posts: 9,402

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

Posted in reply to Nasser_alfea

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 110 views
  • 1 like
  • 3 in conversation