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