Create Moving Average with the SAS Hash Object

There are many ways to create moving averages in SAS. In previous posts, I write about Proc Expand, Proc SQL and Temporary Array approaches. In this post, I will demonstrate how to do so with the hash object.

I use the following example data in the examples.

data have;
input time value;
datalines;
0  100 
1  101 
2  103 
3  107 
4  116 
5  108 
6  113 
7  106 
8  110 
9  115 
10 121 
11 117 
12 123 
13 127 
14 121 
15 126 
16 131 
17 125 
18 125 
19 130 
20 135 
;

Moving Average Using Proc Expand

My favorite way to create moving averages in SAS is Proc Expand. In the code below, I create a backward and a centered moving average with very little coding. However, Proc Expand is part of the SAS/OR software. And the OR license is quite expensive. Therefore, we need Base SAS alternatives.

proc expand data = have out = MoveAv method = none;
   id time;
   convert value = MovAv   / transformout = (movave 3) ;
   convert value = CMovAv  / transformout = (cmovave 3);
run;
 
proc sgplot data = MoveAv;
   series x = time y = MovAv;
   series x = time y = CMovAv;
run;

Backward Moving Average Using the Hash Object

Let us create the same data as above using the SAS hash object. First we declare the hash object h. We specify time as the key and value as the data variable. Next, I read the input data sequentially and initialize s and d to zero. Then I loop from t = -2 to the current value of time. For each t in the loop, I look up the corresponding value in h and add that to s. For each succesful lookup, I add 1 to d. Finally, I divide s by d.

The result is identical to the three-period backward moving average from Proc Expand above.

data MovAv(drop = s t);
 
   if _N_ = 1 then do;
      dcl hash h(dataset : "have");
      h.definekey("time");
      h.definedata("value");
      h.definedone();
   end;
 
   set have;
   s = 0; d = 0;
 
   do t = time - 2 to time;
      value = .;
      if h.find(key : t) = 0 then do;
         s = sum(s, value);
         d + 1;
      end;
   end;
 
   MovAv = divide(s, d);
 
run;

Centered Moving Average Using the Hash Object

Let us tweak the code above to create a centered moving average using the hash object. Luckily this is very easy with the approach above. We simply change the window of which we look up data in h. In the code below, I loop from t – 1 to t + 1. This gives me a centered moving average using the previous, current, and next observation in the data. Other than that, the code is similar to the snippet above.

data CMovAv(drop = s t);
 
   if _N_ = 1 then do;
      dcl hash h(dataset : "have");
      h.definekey("time");
      h.definedata("value");
      h.definedone();
   end;
 
   set have;
   s = 0; d = 0;
 
   do t = time - 1 to time + 1;
      value = .;
      if h.find(key : t) = 0 then do;
         s = sum(s, value);
         d + 1;
      end;
   end;
 
   MovAv = divide(s, d);
 
run;

Summary

In this post, we demonstrate how to use the hash object to create moving averages. The technique does not restrict itself to backward/centered moving averages. We can create all kinds of rolling statics with this approach. The fact that the SAS hash object allows explicit calls and its dynamic structure allow this approach to work.

The book Data Management Solutions Using SAS Hash Table Operations has an entire chapter on aggregates. Also, the article Data Aggregation Using the SAS Hash Object is devoted to calculating descriptive statistics with the hash object.

You can download the entire code from this post here.