Popular Topics
Popular Topics
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;
Subscribe to:
Post Comments (Atom)
2 comments:
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
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
Post a Comment