Welcome to RunSubmit.com

Question

1

I have gotten the results I want with padding the data to fill in missing days, and then using arrays within a data step. I have also used PROC SQL merging a data set with itself multiple times.

edited Oct 31 at 13:25

jay.l.stevens♦
783●8

asked Oct 22 at 14:22

SBloom♦
458●6

Sam · Accepted Answer · 2009-10-22 19:14:32Z UTC

3

If you have SAS/ETS, it calculates a variety of moving averages: http://support.sas.com/onlinedoc/913/getDoc/en/etsug.hlp/expand_sect26.htm

So if dataset IN has the variable X you can calculate the moving average:

proc expand data=in out=movavg;
     convert x=mv_xavg /method=none transformout=(movave 7);
run;

answered Oct 22 at 19:14

Sam
96●1

Hey Sam. Does this deal with missing data points as well. I think I saw you mentioned that expand can do that as well. – Moderator♦ Oct 22 at 19:43

1

Yes, it can do simple imputation, but it is important that the imputation occurs at the same time as any transformation so missing values are treated as missing for conversions in the same way that the sum function does. From earlier, it can aggregate to a consistent time interval or fill in missing intervals. data air; set sashelp.air; if mod(n,5) in (0,1) then delete; run; proc expand data=air out=exp_air1 from=month to=month; id date; convert date/method=none; convert air=orig/ method=none ; convert air=mv_airavg /method=none transformout=(movave 3); run; – Sam Oct 22 at 20:25

mr_sas · Answer 2 · 2009-10-22 18:20:26Z UTC

http://support.sas.com/kb/25/027.html

data ds1; do patient='A','B','C'; do month=1 to 7; num=int(ranuni(0)*10); output; end; end; run;

proc sort; by patient; run;

%let n = 4;

data ds2; set ds1; by patient; retain num_sum 0; if first.patient then do; count=0; num_sum=0; end; count+1; last&n=lag&n(num); if count gt &n then num_sum=sum(num_sum,num,-last&n); else num_sum=sum(num_sum,num); if count ge &n then mov_aver=num_sum/&n; else mov_aver=.; run;

title 'Moving average within BY-Group'; proc print; run;

SBloom · Answer 3 · 2009-10-23 13:24:20Z UTC

I'm liking the PROC EXPAND. I have played around with some of the differnt options.

data test;
format date date.;
do date='01jan09'd to '01jan10'd;
  visits=floor(sin(date/100)*100+ (uniform(0)*10))+100 ;
if mod(date,55) = 0 then visits=.;
if mod(date,15) ne 0 then output;
end;
run;


proc expand data=test out=movavg;     
convert visits=mv_7  /method=none transformout=(movave  7);     /** 7 day moving average - including 6 prior days and current day **/
convert visits=mv_7_1 /method=none transformout=(lag movave 7); /** 7 day moving average - including 7 prior days **/
convert visits=mv_31_1 /method=none transformout=(CMOVAVE  31); 
convert visits=w_mv_7_1 /method=none transformout=(lag movave(64 32 16 8 4 2 1)); /** weighted 7 day moving average - including 7 prior days **/
run;

axis1 order=('01jan09'd to '01jan10'd by month);
symbol1 color=black interpol=join value=none;
symbol2 color=blue  interpol=join value=none;
symbol3 color=green interpol=join value=none;
symbol4 color=red   interpol=join value=none;

proc gplot;
plot (visits mv_31_1 mv_7_1 w_mv_7_1) * date/ overlay haxis=axis1 legend=bottom;
run;

Does anyone know a staight forward way to compute a moving average?

Remember to vote up questions/answers you find interesting or helpful (requires 15 reputation points)

3 Answers