data test;
infile datalines;
input Rec $ RptDate date9. Sales Bonus ;
format rptdate date9.;
tot=sum(of sales,bonus);
return;
datalines;
225 1Jun2014 5 2
226 1Jul2014 2 2
227 1Aug2014 1 3
228 1Sep2014 4 2
229 1Oct2014 5 1
230 1Nov2014 6 2
;
run;
proc sort =test out=test2;
by Rec RptDate;
run;
proc expand =test2 out=test3;
id RptDate;
by Rec;
convert tot=tot_avg/ transformout=(movave 3 trimleft 2);
run;
My proc expand for some reason will not produce the 3 month tot_avg which is based on the sum of sales + bonus. I am attempting a 3 month moving average column
I get this instead
REC RptDate tot_avg Sales Bonus Tot
225 01JUN2014 5 2 7
226 01JUL2014 2 2 4
227 01AUG2014 1 3 4
228 01SEP2014 4 2 6
229 01OCT2014 5 1 6
230 01NOV2014 6 2 8
You have a BY statement for the REC but each REC only has one record, so that doesn't make sense.
Your code is buggy, please ensure it runs when posting sample code.
You don't need the "BY" and "ID". Refer this document if you want to learn more http://support.sas.com/resources/papers/proceedings10/093-2010.pdf
You need at least three observations in each BY group. For a discussion, examples, and links to other resources, see
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.