topic Re: Creating a Rolling 12 Month Total in SAS Programming

Creating a Rolling 12 Month Total

cbrotz — Thu, 06 Jul 2017 15:02:07 GMT

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.

Re: Creating a Rolling 12 Month Total

Reeza — Thu, 06 Jul 2017 15:05:28 GMT

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

Re: Creating a Rolling 12 Month Total

cbrotz — Thu, 06 Jul 2017 15:43:43 GMT

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

Re: Creating a Rolling 12 Month Total

Reeza — Thu, 06 Jul 2017 15:47:20 GMT

LMGTFY : PROC EXPAND SAS DOCUMENTATION

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

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

Re: Creating a Rolling 12 Month Total

dcruik — Thu, 06 Jul 2017 17:13:02 GMT

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;

Re: Creating a Rolling 12 Month Total

cbrotz — Thu, 06 Jul 2017 17:35:23 GMT

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.

Re: Creating a Rolling 12 Month Total

Reeza — Thu, 06 Jul 2017 20:28:25 GMT

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-industry-codes/td-p/293717

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

Re: Creating a Rolling 12 Month Total

cbrotz — Thu, 06 Jul 2017 22:08:06 GMT

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

Re: Creating a Rolling 12 Month Total

cbrotz — Thu, 06 Jul 2017 22:09:12 GMT

Oh sorry I did not know how to delete and change the solution...

Re: Creating a Rolling 12 Month Total

Reeza — Thu, 06 Jul 2017 22:48:00 GMT

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.

Re: Creating a Rolling 12 Month Total

cbrotz — Thu, 06 Jul 2017 22:55:23 GMT

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;

Re: Creating a Rolling 12 Month Total

Reeza — Thu, 06 Jul 2017 23:05:58 GMT

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;

Re: Creating a Rolling 12 Month Total

cbrotz — Thu, 06 Jul 2017 23:19:08 GMT

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;

Re: Creating a Rolling 12 Month Total

Reeza — Thu, 06 Jul 2017 23:36:18 GMT

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.

Re: Creating a Rolling 12 Month Total

cbrotz — Thu, 06 Jul 2017 23:36:40 GMT

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;

Re: Creating a Rolling 12 Month Total

Reeza — Thu, 06 Jul 2017 23:40:21 GMT

@cbrotz wrote:

Perhaps once I get a working example I will understand it better.

Both examples posted are fully worked examples with BY groups/breaks exactly like yours with also exactly a 12 month lag. The only difference is the source data set and variables and replace MEAN with the word SUM.

Re: Creating a Rolling 12 Month Total

cbrotz — Thu, 06 Jul 2017 23:40:36 GMT

Reeza - thank you for your patience - this is not how I usually code so I had to work through it a bit but I think this part is SOLVED. I would tag you but I do not see a way to do that. 🙂 Have a good evening.

Re: Creating a Rolling 12 Month Total

Reeza — Thu, 06 Jul 2017 23:42:21 GMT

@cbrotz wrote:

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; *-> This line is wrong;

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

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

format roll_tot dollar12.2;

run;

Re: Creating a Rolling 12 Month Total

cbrotz — Thu, 06 Jul 2017 23:45:19 GMT

Thank you!

Re: Creating a Rolling 12 Month Total

mkeintz — Thu, 13 Jul 2017 19:38:35 GMT

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.