topic Re: Create 12 Month Rolling Total that Accounts for Missing Months in SAS Programming

Create 12 Month Rolling Total that Accounts for Missing Months

cbrotz — Tue, 11 Jul 2017 14:18:55 GMT

Posts: 27

Re: Creating a Rolling 12 Month Total

3m ago

I am currently working with the following code. Does anyone know how to modify to account for missing months? We use SAS EG 7.13

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;

In the following data for example if a month is missing, roll_tot needs to calculate based on 11 months....or whatever span of time is missing. Thank you!

group_st fcst_pr yyyymm sum_of_bi count roll_tot

3000011   DQFS   201301   354.4000   1   $354.40
3000011   DQFS   201302   94.0000   2   $448.40
3000011   DQFS   201303   48.0300   3   $496.43
3000011   DQFS   201304   119.0000   4   $615.43
3000011   DQFS   201305   261.9400   5   $877.37
3000011   DQFS   201306   359.2200   6   $1,236.59
3000011   DQFS   201307   146.0000   7   $1,382.59
3000011   DQFS   201308   198.4500   8   $1,581.04
3000011   DQFS   201309   143.2500   9   $1,724.29
3000011   DQFS   201310   65.2700   10   $1,789.56
3000011   DQFS   201311   292.7000   11   $2,082.26
3000011   DQFS   201312   71.0000   12   $2,153.26
3000011   DQFS   201401   170.2200   13   $1,969.08
3000011   DQFS   201403   205.4000   14   $2,080.48 should be 2032.45
3000011   DQFS   201404   652.5000   15   $2,684.95 should be 2565.95
3000011   DQFS   201405   18.5200   16   $2,584.47   should be 2322.53
3000011   DQFS   201406   64.2000   17   $2,386.73   should be 2027.51

Re: Create 12 Month Rolling Total that Accounts for Missing Months

Reeza — Tue, 11 Jul 2017 17:48:00 GMT

Did you ever figure out if you have SAS/ETS?

proc setinit;run;

Re: Create 12 Month Rolling Total that Accounts for Missing Months

cbrotz — Tue, 11 Jul 2017 18:29:58 GMT

Is that what I need to use Proc Expand? I think we must have it because I tried running proc expand a few different ways but am not getting the right output yet. There are just so many options I need to read through all of them.

This is the code I am trying to modify. Every time I use from=month, I get disconnected from the server....

proc expand
   data=work.fix_the_date /* input dataset */
   out=work.add_roll_12; /* output dataset */
     /* input timeframe - monthly */
   *align=end;    /* alignment of timeframe - end of month */
;
by group_status fcst_prod_grp ; * each time series is grouped;
id sasdate; * specify time series variable;
convert sum_of_billings / method=none; * do not interpolate missing values;
* calculate 3-period moving sum without missing values, and set the first 2 values at ;
* beginning of each by-group to missing;
convert sum_of_billings=calc_roll3 / method=none transformout=(nomiss movsum 3);
run;

Re: Create 12 Month Rolling Total that Accounts for Missing Months

Reeza — Tue, 11 Jul 2017 18:32:41 GMT

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

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

The first paper and the documentation have good examples. The code you posted looks a bit more complex than I think you'd need.

Re: Create 12 Month Rolling Total that Accounts for Missing Months

cbrotz — Tue, 11 Jul 2017 18:43:58 GMT

I need code that will count correctly. Neither of the code examples - either the array or the proc expand are giving the correct totals when a month is skipped. In the data example, if the 11 months are added together accounting for the skipped month, it should total 2032.45. So far, I do not see a code example that is performing this calculation correctly.

Re: Create 12 Month Rolling Total that Accounts for Missing Months

Reeza — Tue, 11 Jul 2017 19:34:46 GMT

Then use TIMESERIES or a data step/proc sql to add the missing months would be the easiest solution IMO.

Re: Create 12 Month Rolling Total that Accounts for Missing Months

Reeza — Tue, 11 Jul 2017 21:56:44 GMT

@cbrotz wrote:

So far, I do not see a code example that is performing this calculation correctly.

You'll rarely find exactly what you're looking for, you'll need to usually cobble together a couple of answers for anything above basic coding.

Here's a fully worked example that's I've tested and posted online.

https://gist.github.com/statgeek/07a3708dee1225ceb9d4aa75daab2c52