Rolling Averages 2

[Medvedeff]

I am trying to figure out how to best write code for a rolling average and I cant quite figure it out.

I am trying to create a new variable, "average", which represents the average of the last 250 observations of an existing variable, "price".

So essentially, I need the 251st observation of "Average" to be based on values 1-250 of "Price" ... and then the 252nd observation of "Average" to be based on values 2-251 of "Price".

Any suggestions on how to go about doing this? Thank you in advance for your help.

 

[ChrisW75]

This is a tricky one I think.
One method I can think of is to use an array to hold the values for the last 250 records, then each iteration shift all the values down 1 place and load the current one in.
You'd have to retain the values obviously.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[krispi]

Intriguing question. I haven't tested this code, which I found here:

http://www.science.gmu.edu/stat362/02f/sas12.htm

but worth a try...

%MACRO MovAve(ds,v,ln);
%* This adds to the specified dataset, the moving
   average of the specified variable over a given
   length.
   The name of the new variable will be MAv, where
   "v" is the name of the variable whose moving
   average is being computed.                  ;
%* The parameters are:                         ;
%* ds  - the dataset to be used (it is changed by having an 
         additional variable);
%* v   - the variable whose moving average is to be computed;
%* ln  - the length of the moving average window plus 1
         (that is, for a 12-period moving average, ln=13);
DATA &ds;
  SET &ds;
  SUMv+&v;
DATA &ds;
  IF _N_=1 THEN DO;
      DO n=1 TO &ln-1;
         SET &ds; MA&v=SUMv/n;  OUTPUT;  
      END;  
  END;
  ELSE DO;
      MERGE &ds &ds(FIRSTOBS=&ln RENAME=(SUMv=SUMv2));
      IF SUMv2 ^= . ;
      MA&v=(SUMv2-SUMv)/(&ln-1);  OUTPUT;  
  END;
  DROP n SUMv SUMv2;
%MEND MovAve;

%MovAve(salesdata,sales,5)

Good luck!

Chris

 

[kdt82]

No need for the data step here: SAS provides a good proc for this type of problem.

The following code should work for you, just enter the name of the dataset you want to analyze and the output dataset name:

proc expand data =  
             out = ; 
   convert price = average/ transformout = (movave 250); 
   run;
proc print;run;

 

[ChrisW75]

Nice one Kdt82, had a little trouble finding documentation on that proc, but eventually found it here:-

http://support.sas.com/onlinedoc/913/getDoc/en/etsug.hlp/expand_index.htm#idxexp0001

Medvedev - Proc Expand seems to be a SAS/ETS procedure, so if you don't have that package, this procedure won't work for you, and you'll have to try Krispi's solution.
To find out what SAS packages you have installed run this and then check the log.

proc setinit;
run;

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.