BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cbrotz
Pyrite | Level 9

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.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
cbrotz
Pyrite | Level 9

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

19 REPLIES 19
Reeza
Super User

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

cbrotz
Pyrite | Level 9

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

Reeza
Super User

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

dcruik
Lapis Lazuli | Level 10

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;
cbrotz
Pyrite | Level 9

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.

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Reeza
Super User

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

 

 

cbrotz
Pyrite | Level 9

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

 

 

 

Reeza
Super User

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. 

cbrotz
Pyrite | Level 9

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;

Reeza
Super User

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;
cbrotz
Pyrite | Level 9

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;

 

 

 

Reeza
Super User

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. 

 

cbrotz
Pyrite | Level 9

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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