DATA Step, Macro, Functions and more

Create 12 Month Rolling Total that Accounts for Missing Months

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

Create 12 Month Rolling Total that Accounts for Missing Months

 
Posts: 27
 
Re: Creating a Rolling 12 Month Total
 
 

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


Accepted Solutions
Solution
‎07-12-2017 11:07 AM
Super User
Posts: 19,815

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

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

View solution in original post


All Replies
Super User
Posts: 19,815

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

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

 

proc setinit;run;
Contributor
Posts: 46

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

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;

Super User
Posts: 19,815

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

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

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

 

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

Contributor
Posts: 46

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

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.

 

 

Solution
‎07-12-2017 11:07 AM
Super User
Posts: 19,815

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

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

Super User
Posts: 19,815

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


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

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 198 views
  • 1 like
  • 2 in conversation