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;
run;
%let n = 4;
data ds2;
set ds1;
by account_id;
retain num_sum 0;
if first.account_id then do;
count=0;
num_sum=0;
end;
count+1;
last&n=lag&n(num);
if count gt &n then num_sum=sum(num_sum,num,-last&n);
else num_sum=sum(num_sum,num);
if count ge &n then mov_aver=num_sum/&n;
else mov_aver=.;
run;

2 comments:

Nandhini said...

Updating with the latest technology and implementing it is the only way to survive in our niche. Thanks for making me this article. You have done a great job by sharing this content in here. Keep writing article like this.
SAS Training in Chennai | SAS Course in Chennai

Unknown said...

Updating with the latest technology and implementing it is the only way to survive in our niche. Thanks for making me this article.
Node JS training in chennai | Node JS training institute in chennai