Hope this provides the result you are looking for. Modified the code at http://support.sas.com/kb/41/380.html by adding a macro to support multiple columns. Code to create the input dataset has also been included so you can see the entire process. After running this program see the log file for code written by the macro. options mprint; /* create monthly sales data from January 2008 to December 2010 */ data work.test; do mo_period = '01jan2008'd to '31dec2010'd; mo_period = intnx('month', mo_period, 0, 'END'); /* create 3 colums, one for each value to be evaluated. */ do eval_column = 'col_1', 'col_2', 'col_3'; sales = round(ranuni(1234567) * 1000, .01); output; end; end; format mo_period monyy7. sales comma10.2; run; /* confirm output is as expected. */ proc print data=work.test(obs=20); run; /* convert data to across format. */ proc transpose data=work.test out=work.test1(drop=_name_); var sales; by mo_period; id eval_column; run; /* confirm output is as expected. */ proc print data=work.test1(obs=10); run; /*specify the number of periods in the rolling sum and average*/ %let roll_num = 12; /*specify the number of columns to process */ %let columns = 3; %macro tst; data work.new; set work.test1; /*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*/ /* for the first observation only, create an array for each column to be evaluated.*/ if (_n_ = 1) then do; %do i = 1 %to &columns; array summed_&i [&roll_num] _temporary_; format roll_sum_&i roll_avg_&i comma10.2; %end; end; /*alternatively, a non-temporary array can be used but 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; %do i = 1 %to &columns; /*assign value to proper element of the array*/ summed_&i = col_&i; /*start summing once &roll_num values have been read from the data set*/ if (_N_ >= &roll_num) then do; roll_sum_&i = sum(of summed_&i ); roll_avg_&i = mean(of summed_&i ); roll_std_&i = std(of summed_&i ); end; %end; run; %mend tst; %tst; options ls=160 ps=30; proc print; run;
... View more
