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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
Reeza
Super User

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

 

proc setinit;run;
cbrotz
Pyrite | Level 9

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;

cbrotz
Pyrite | Level 9

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.

 

 

Reeza
Super User

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

Reeza
Super User

@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

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
  • 6 replies
  • 2155 views
  • 1 like
  • 2 in conversation