I am compiling a list of leading data providers. This is the current list. If your favorite data providers are not included here and you feel they should be, please contact me at email@example.com. Thanks. Dr. Zhou.
Wednesday, June 13, 2012
Calculate moving average: SAS vs Oracle
Oracle analytic windows functions provide a great way to calculate cross-row information such as moving average. The following examples show that using Oracle analytic function to calculate moving average is more compact, readable and less error-prone than using SAS scripts.
1. Calculating Moving Average using Oracle Analytic Function
create table ds2 as
select a.*, avg(num) over(partition by account_id order by month rows between 4 preceding and current row) mov_aver from ds1 a;
2. Calculating Moving Average using SAS
proc sort data=ds1;
by account_id month;
%let n = 4;
retain num_sum 0;
if first.account_id then do;
if count gt &n then num_sum=sum(num_sum,num,-last&n);
if count ge &n then mov_aver=num_sum/&n;