Sample 41380: Calculate rolling sums and averages using arrays
 |  |  |  |  |  |
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
| Type: | Sample |
| Topic: | SAS Reference ==> Statements ==> Statements |
| Date Modified: | 2010-11-04 15:17:22 |
| Date Created: | 2010-10-25 10:09:04 |
Operating System and Release Information
| Product Family | Product | Host | SAS Release | |
| Starting | Ending | |||
| SAS System | Base SAS | Microsoft Windows 2000 Server | ||
| Microsoft Windows 2000 Professional | ||||
| Microsoft Windows NT Workstation | ||||
| Microsoft Windows Server 2003 Datacenter Edition | ||||
| Microsoft Windows Server 2003 Enterprise Edition | ||||
| Microsoft Windows Server 2003 Standard Edition | ||||
| Microsoft Windows Server 2003 for x64 | ||||
| Microsoft Windows Server 2008 | ||||
| Microsoft Windows Server 2008 for x64 | ||||
| Microsoft Windows XP Professional | ||||
| Windows 7 Enterprise 32 bit | ||||
| Windows 7 Enterprise x64 | ||||
| Windows 7 Home Premium 32 bit | ||||
| Windows 7 Home Premium x64 | ||||
| Windows 7 Professional 32 bit | ||||
| Windows 7 Professional x64 | ||||
| Windows 7 Ultimate 32 bit | ||||
| Windows 7 Ultimate x64 | ||||
| Windows Millennium Edition (Me) | ||||
| Windows Vista | ||||
| Windows Vista for x64 | ||||
| 64-bit Enabled AIX | ||||
| 64-bit Enabled HP-UX | ||||
| 64-bit Enabled Solaris | ||||
| ABI+ for Intel Architecture | ||||
| AIX | ||||
| HP-UX | ||||
| HP-UX IPF | ||||
| IRIX | ||||
| z/OS | ||||
| OpenVMS VAX | ||||
| Microsoft® Windows® for 64-Bit Itanium-based Systems | ||||
| Z64 | ||||
| Microsoft Windows 2000 Datacenter Server | ||||
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | ||||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | ||||
| Microsoft Windows XP 64-bit Edition | ||||
| Microsoft® Windows® for x64 | ||||
| OS/2 | ||||
| Microsoft Windows 95/98 | ||||
| Microsoft Windows 2000 Advanced Server | ||||
| Linux | ||||
| Linux for x64 | ||||
| Linux on Itanium | ||||
| OpenVMS Alpha | ||||
| OpenVMS on HP Integrity | ||||
| Solaris | ||||
| Solaris for x64 | ||||
| Tru64 UNIX | ||||