The sample code on the Full Code tab illustrates how to calculate rolling sums and rolling averages by defining an array to hold the values for the most recent number of desired periods. Once the array contains the desired values, the calculation of the sum, using the SUM function, and the average, using the MEAN function, is straight-forward. You can simply pass the array to either function to obtain the desired statistic:

  _sum_ = sum(of array_name[*]);  

  _avg_ = mean(of array_name[*]);  

This sample emphasizes the logic needed to always have the most recent periods in the array before calculating the next set of rolling statistics. The concept of removing the oldest value and replacing it with the most recent value is commonly referred to as FIFO - First In, First Out.

Once the array has been populated with the initial number of periods, the rolling statistics are calculated. A counter variable is used to keep track of and specify the element in the array containing the oldest value and to which the new most recent value should be assigned. The new value over-writes the oldest value. After an assignment is performed, new rolling statistics are calculated. This process continues until the last value contributing to the statistics has been read from the data source.

Note: Other rolling statistics could also be calculated, such as the standard deviation:

  _std_ = std(of array_name[*]);  

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

In the first DATA step below, sample sales data is created for 36 months.

In the second DATA step, an array is created with the same number of elements as the desired number of periods to be used in calculating a rolling sum and average. Once the array has initially been populated with the desired number of periods, the next period replaces the oldest period within the array. In this sample, the desired number of periods is 12, so after the rolling sum and average are calculated on the first 12 periods, the 13th period will replace the first period and a new rolling sum and average are calculated. Then the 14th period replaces the second period, and a new rolling sum and average are then calculated, and so on.

/* Create monthly sales data from January 2008 to December 2010 */
data test;
   do mo_period = '01jan2008'd to '31dec2010'd;
      sales = round(ranuni(1234567) * 1000, .01);
      mo_period = intnx('month', mo_period, 0, 'END');
      output;
   end;
   format mo_period monyy7. sales comma10.2;
run;

/* Specify the number of periods in the rolling sum and average */
%let roll_num = 12;

data new;
   set test;
/* Create array with specific number of elements. */
/* Passing _temporary_ arrays as arguments to functions is not supported */
/* until SAS 9.2. */
/* If the array is a _TEMPORARY_ array, the elements are automatically retained */
   array summed[&roll_num] _temporary_;

/* Alternatively, a non-temporary array can be used but it must be retained: */
/* array summed[&roll_num];*/
/* retain summed;*/

/* E represents the element of the array to assign a sales value. */
/* Increment it by one unless it is equal to &roll_num, at which point */
/* start over and assign it a value of 1. This causes the oldest period to */
/* be replaced by the newest period once &roll_num periods have been read. */
   if E = &roll_num then E = 1;
   else E + 1;
/* Assign value to proper element of the array */
   summed[E] = sales;
/* Start summing once &roll_num values have been read from the data set */
   if _N_ >= &roll_num then do;
      roll_sum = sum(of summed[*]);
      roll_avg = mean(of summed[*]);
   end;
   format roll_sum roll_avg comma10.2;
run;

proc print; run;

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Obs    mo_period         sales     E      roll_sum      roll_avg

  1     JAN2008         684.94     1           .             .
  2     FEB2008         515.42     2           .             .
  3     MAR2008         894.10     3           .             .
  4     APR2008           7.43     4           .             .
  5     MAY2008         129.75     5           .             .
  6     JUN2008         829.85     6           .             .
  7     JUL2008         168.86     7           .             .
  8     AUG2008          55.17     8           .             .
  9     SEP2008          61.55     9           .             .
 10     OCT2008         867.91    10           .             .
 11     NOV2008         669.72    11           .             .
 12     DEC2008         258.08    12      5,142.78        428.57
 13     JAN2009         454.17     1      4,912.01        409.33
 14     FEB2009         861.42     2      5,258.01        438.17
 15     MAR2009          72.81     3      4,436.72        369.73
 16     APR2009         860.74     4      5,290.03        440.84
 17     MAY2009         518.59     5      5,678.87        473.24
 18     JUN2009         354.67     6      5,203.69        433.64
 19     JUL2009         665.90     7      5,700.73        475.06
 20     AUG2009         707.65     8      6,353.21        529.43
 21     SEP2009         133.17     9      6,424.83        535.40
 22     OCT2009         648.15    10      6,205.07        517.09
 23     NOV2009         475.26    11      6,010.61        500.88
 24     DEC2009         260.54    12      6,013.07        501.09
 25     JAN2010         774.96     1      6,333.86        527.82
 26     FEB2010          37.21     2      5,509.65        459.14
 27     MAR2010         537.34     3      5,974.18        497.85
 28     APR2010         738.63     4      5,852.07        487.67
 29     MAY2010         927.31     5      6,260.79        521.73
 30     JUN2010         264.14     6      6,170.26        514.19
 31     JUL2010         145.93     7      5,650.29        470.86
 32     AUG2010         999.54     8      5,942.18        495.18
 33     SEP2010         573.12     9      6,382.13        531.84
 34     OCT2010         952.50    10      6,686.48        557.21
 35     NOV2010         194.42    11      6,405.64        533.80
 36     DEC2010         517.72    12      6,662.82        555.24

Operating System and Release Information