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