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
Then use TIMESERIES or a data step/proc sql to add the missing months would be the easiest solution IMO.
Did you ever figure out if you have SAS/ETS?
proc setinit;run;
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;
http://support.sas.com/resources/papers/proceedings10/093-2010.pdf
The first paper and the documentation have good examples. The code you posted looks a bit more complex than I think you'd need.
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.
Then use TIMESERIES or a data step/proc sql to add the missing months would be the easiest solution IMO.
@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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.