Calculating moving average using do loop in SAS

Question

I am trying to find a way to calculate a moving average using SAS do loops. I am having difficulty. I essentially want to calculate a 4 unit moving average.

DATA data;
   INPUT a b;
   CARDS;
1 2
3 4 
5 6 
7 8 
9 10
11 12
13 14
15 16
17 18
;
run;    

data test(drop = i);
   set data;
   retain c 0;
   do i = 1 to _n_-4;
      c = (c+a)/4;
   end;
run;

proc print data = test;
run;

Answer 1

Straight from Cody's Collection of Popular Programming Tasks and How to Tackle them.

*Presenting a macro to compute a moving average;


%macro Moving_ave(In_dsn=,     /*Input data set name          */
                  Out_dsn=,    /*Output data set name         */
                  Var=,        /*Variable on which to compute
                                 the average                  */
                  Moving=,     /* Variable for moving average */
                  n=           /* Number of observations on which
                                  to compute the average      */);
   data &Out_dsn;
      set &In_dsn;
        ***compute the lags;
      _x1 = &Var;
      %do i = 1 %to &n - 1;
         %let Num = %eval(&i + 1);
          _x&Num = lag&i(&Var);
      %end;

        ***if the observation number is greater than or equal to the
           number of values needed for the moving average, output;
   if _n_ ge &n then do;
      &Moving = mean (of _x1 - _x&n);
      output;
   end;
   drop _x:;
   run;
%mend Moving_ave;


*Testing the macro;
%moving_Ave(In_dsn=data,
            Out_dsn=test,
            Var=a,
            Moving=Average,
            n=4)

Answer 2

One option is to use the merge-ahead:

DATA have;
 INPUT a b;
CARDS;
1 2
3 4 
5 6 
7 8 
9 10
11 12
13 14
15 16
17 18
;
run;

data want;
merge have have(firstobs=2 rename=a=a_1) have(firstobs=3 rename=a=a_2) have(firstobs=4 rename=a=a_3);
c = mean(of a:);
run;

Merge the data to itself, each time the merged dataset advancing one - so the 2nd starts with 2, third starts with 3, etc. That gives you all 4 'a' on one line.

Answer 3

SAS has a lag() function. What this does is create the lag of the variable it is applied to. SO for example, if your data looked like this:

DATA data;
 INPUT a ;
 CARDS;
1 
2 
3
4
5  
;

Then the following would create a lag one, two, three etc variable;

data data2; 
set data;
a_1=lag(a);
a_2=lag2(a);
a_3=lag3(a);
drop b;
run;

would create the following dataset

a a_1 a_2 a_3
1 . . .
2 1 . .
3 2 1 .
4 3 2 1

etc. Moving averages can be easily calculated from these. Check out http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000212547.htm

(Please note, I did not get a chance to run the codes, so they may have errors.)