DATA Step, Macro, Functions and more

3 month moving average assistance

Reply
Frequent Contributor
Posts: 141

3 month moving average assistance

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

Super User
Posts: 19,789

Re: 3 month moving average assistance

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.

Frequent Contributor
Posts: 136

Re: 3 month moving average assistance

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

Thanks,
Suryakiran
SAS Super FREQ
Posts: 3,752

Re: 3 month moving average assistance

You need at least three observations in each BY group. For a discussion, examples, and links to other resources, see 

"Compute a moving average in SAS."

Ask a Question
Discussion stats
  • 3 replies
  • 126 views
  • 2 likes
  • 4 in conversation