DATA Step, Macro, Functions and more

Creating a Rolling 12 Month Total

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

Creating a Rolling 12 Month Total

I have the following data attached and am trying to create the Rolling 12 Total column to the right based off of the date and Sum of Billing data to the left.  I did see an example using an array which I am not real familiar with using.  The total accumulates for only the last 12 months....Thanks for the help.

 

 

 


Accepted Solutions
Solution
‎07-06-2017 01:35 PM
Contributor
Posts: 46

Re: Creating a Rolling 12 Month Total

Yes this is what I was looking for.  I did not know you could "lag" that many times.  Yes the numbers were given to me to match up to and they could be calculated incorrectly.

View solution in original post


All Replies
Super User
Posts: 19,862

Re: Creating a Rolling 12 Month Total

PROC EXPAND. The third example in the documentation illustrates the CONVERT statement. 

Contributor
Posts: 46

Re: Creating a Rolling 12 Month Total

I don't know what "documentation" you are referring to.  Can you give me a link?

Super User
Posts: 19,862

Re: Creating a Rolling 12 Month Total

LMGTFY : PROC EXPAND SAS DOCUMENTATION

 

http://documentation.sas.com/?docsetId=etsug&docsetTarget=etsug_expand_examples04.htm&docsetVersion=...

 

If you need more information, try a user written paper from LexJansen.com. In general, you'll find many papers on any topic at LexJansen.com.

 

http://support.sas.com/resources/papers/proceedings10/093-2010.pdf

Frequent Contributor
Posts: 130

Re: Creating a Rolling 12 Month Total

You can alway use the by-row processing and some lag functions.  I would take a look at your values of ROLLING 12 TOTAL in your attachment because I'm getting a different rolling 12 month value starting at 201403 than what is in the file, unless I'm misunderstanding the calculation of ROLLING 12 TOTAL.  The following code will give you a rolling 12-month sum of the BILLINGS values:

data input;
	format Date $6. Billings best8.;
	input Date Billings ;
	datalines;
201301 354.4
201302 94
201303 48.03
201304 119
201305 261.94
201306 359.22
201307 146
201308 198.45
201309 143.25
201310 65.27
201311 292.7
201312 71
201401 170.22
201403 205.4
201404 652.5
201405 18.52
201406 64.2
201408 88.2
201409 104.2
201410 79.5
201411 952.2
201412 106
;
run;

proc sort data=input;
	by Date;
run;

data output (keep=Date Billings Rolling_12);
set input;
	by Date;

retain x1-x11 Rolling_12;
x1 =lag1(Billings);
x2 =lag2(Billings);
x3 =lag3(Billings);
x4 =lag4(Billings);
x5 =lag5(Billings);
x6 =lag6(Billings);
x7 =lag7(Billings);
x8 =lag8(Billings);
x9 =lag9(Billings);
x10=lag10(Billings);
x11=lag11(Billings);

If _N_ = 1 then Rolling_12 = Billings;
	Else if _N_ < 12 then Rolling_12 + Billings;
	Else Rolling_12 = sum(Billings,x1,x2,x3,x4,x5,x6,x7,x8,x9,x10,x11);
run;

proc print data=output noobs;
run;
Solution
‎07-06-2017 01:35 PM
Contributor
Posts: 46

Re: Creating a Rolling 12 Month Total

Yes this is what I was looking for.  I did not know you could "lag" that many times.  Yes the numbers were given to me to match up to and they could be calculated incorrectly.

Trusted Advisor
Posts: 1,022

Re: Creating a Rolling 12 Month Total

I think you can simply a great deal.  You don't need to keep separate queues for 1-month lag, 2-month lag, etc.  You really only need to keep a running total, adding the current record and subtracting the 12-month old record, as in:

 

data want;
  set have;
  rolling_12 + billings - coalesce(lag12(billings),0);
  window_size=min(12,_n_);
run;

 

Since the first 12 observations have lag12(billings)= missing, I should not attempt to subtract those values, so instead I subtract

    coalesce(lag12(billings),0)

 

COALESCE takes the left-most non-missing value from the list of argument, which means the zero for the first 12 obs.

Super User
Posts: 19,862

Re: Creating a Rolling 12 Month Total

Proc expand is much shorter and easier to implement. And will handle BY group processing. Arrays also see simpler to me.

 

If you're in search of an exact answer you can also search on here. For example here's one I answered last year.

 

https://communities.sas.com/t5/Base-SAS-Programming/Generating-rolling-12-month-totals-for-multiple-...

 

@cbrotz please note that accepting your own solution is not correct. You should have accepted the solution that was posted by @dcruik

 

 

Contributor
Posts: 46

Re: Creating a Rolling 12 Month Total

Hi Reeza,

 

I am sorry I am just not following this code.  I modified it to use my sorted data set. Below.

 

PROC SORT DATA=WORK.SUMMARY_RET_DATA;

BY GROUP_STATUS FCST_PROD_GRP YYYYMM;

 

 

data ret_avg12;

set work.summary_ret_data;

by group_status fcst_prod_grp yyyymm;

 

array values(12) _temporary_;

 

if first.fcst_prod_grp then do;

call missing(of values(*));

count=0;

end;

 

count+1;

 

values(mod(count, 12)+1) = open;

 

if count>=12 then average=mean(of values(*));

 

format average dollar12.2;

 

run;

 

This is my output….obviously I am not doing something right….

 

 

 

Super User
Posts: 19,862

Re: Creating a Rolling 12 Month Total

1. Why did you delete all the comments? They explain what's happening and help you modify your code accordingly.  

2. You didn't replace the variable open. Note that there's a variable in your image with the name Open, that's your indicator to tell you something is incorrect. Go back to that step in the solution to see what you should be doing instead. 

Contributor
Posts: 46

Re: Creating a Rolling 12 Month Total

This is the original code that I am using....I did not delete any comments.  I do not know what "open" means.

 

proc sort data=sashelp.stocks out=stocks;
by stock date;
run;

data stocks_avg12;
set stocks;
by stock;

array values(12) _temporary_;

if first.stock then do;
call missing(of values(*));
count=0;
end;

count+1;

values(mod(count, 12)+1) = open;

if count>=12 then average=mean(of values(*));

format average dollar12.2;

run;

proc expand data=stocks_avg12 out=stocks_avg12_method2;
by stock;
id  date;
convert open=average2 / transformout=(movave 12 trimleft 11);
run;

Super User
Posts: 19,862

Re: Creating a Rolling 12 Month Total

See the post right above the one you're using for the detailed comments. To save both of us some time here it is reposted. Run the EXAMPLE first to understand what's happening. I've already stated where the issue is - you're using the variable OPEN from the demo code rather than modifying it your data. This example uses SALES but it's the same concept. 

 

There are more comments than code in this solution. 

 

/* Create monthly sales data from January 2008 to December 2010 */
data test;
   do mo_period = '01jan2008'd to '31dec2010'd;
      sales = round(ranuni(1234567) * 1000, .01);
      mo_period = intnx('month', mo_period, 0, 'END');
      output;
   end;
   format mo_period monyy7. sales comma10.2;
run;

/* Specify the number of periods in the rolling sum and average */
%let roll_num = 12;

data new;
   set test;
/* Create array with specific number of elements. */
/* Passing _temporary_ arrays as arguments to functions is not supported */
/* until SAS 9.2. */
/* If the array is a _TEMPORARY_ array, the elements are automatically retained */
   array summed[&roll_num] _temporary_;

/* Alternatively, a non-temporary array can be used but it must be retained: */
/* array summed[&roll_num];*/
/* retain summed;*/

/* E represents the element of the array to assign a sales value. */
/* Increment it by one unless it is equal to &roll_num, at which point */
/* start over and assign it a value of 1. This causes the oldest period to */
/* be replaced by the newest period once &roll_num periods have been read. */
   if E = &roll_num then E = 1;
   else E + 1;

/* Assign value to proper element of the array */
   summed[E] = sales;

/* Start summing once &roll_num values have been read from the data set */
   if _N_ >= &roll_num then do;
      roll_sum = sum(of summed[*]);
      roll_avg = mean(of summed[*]);
   end;
   format roll_sum roll_avg comma10.2;
run;

proc print; run;
Contributor
Posts: 46

Re: Creating a Rolling 12 Month Total

Ok so I inserted my billing total in place of the word "open"  and the code works but the output I am getting does not break and give me a totals after group_status and fcst_prod_grp and I am not looking for an average but a rolling total at the breaks - it needs to reset....

Do you know what I need to do to get it to give rolling totals and break and reset after the by group?

 

  Again I am sorry I really am just copying what you have here.  Perhaps once I get a working example I will understand it better.

 

proc sort data=sashelp.stocks out=stocks;
by stock date;
run;

data stocks_avg12;
set stocks;
by stock;

array values(12) _temporary_;

if first.stock then do;
call missing(of values(*));
count=0;
end;

count+1;

values(mod(count, 12)+1) = sum_of_billings;

if count>=12 then average=mean(of values(*));

format average dollar12.2;

run;

proc expand data=stocks_avg12 out=stocks_avg12_method2;
by stock;
id  date;
convert open=average2 / transformout=(movave 12 trimleft 11);
run;

 

 

 

Super User
Posts: 19,862

Re: Creating a Rolling 12 Month Total

I can't tell what's your code versus sample code here. Are you working off the sample or your own data now? 

 

You've made  changes in some places but not others so I have no idea. Literally take every line of code (there's less than 12) comment it with what you think is happening based on text, and note what YOUR VARIABLES would be the equivalent and write your code underneath. 

Then comment out/delete the other code. 

 

Contributor
Posts: 46

Re: Creating a Rolling 12 Month Total

So I made a couple more changes below....zeroed out a field I am calling roll_tot at each break and then adding "sum_of_billings to that field to accumulate.  I changed "mean" to "sum".  I am not sure how that is going to affect the overall results but they look good from what I am seeing.

 

data ret_avg12;

set work.summary_ret_data;

by group_status fcst_prod_grp yyyymm;

array values(12) _temporary_;

if first.fcst_prod_grp then do;

call missing(of values(*));

count=0;

roll_tot=0;

end;

count+1;

roll_tot+sum_of_billings;

values(mod(count, 12)+1) = sum_of_billings;

if count>=12 then roll_tot=sum(of values(*));

format roll_tot dollar12.2;

run;

☑ This topic is solved.

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

Discussion stats
  • 19 replies
  • 477 views
  • 6 likes
  • 4 in conversation