topic Re: 3 month moving average assistance in SAS Programming

3 month moving average assistance

Q1983 — Mon, 17 Jul 2017 16:41:17 GMT

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

Re: 3 month moving average assistance

Reeza — Mon, 17 Jul 2017 16:58:52 GMT

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.

Re: 3 month moving average assistance

SuryaKiran — Mon, 17 Jul 2017 18:18:28 GMT

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

Re: 3 month moving average assistance

Rick_SAS — Tue, 18 Jul 2017 14:06:54 GMT

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