DATA Step, Macro, Functions and more

SAS Rolling total or average

Reply
Contributor
Posts: 32

SAS Rolling total or average

Hello,

 

Is there a way I can use SAS to create the 3 months moving average  in my data set.

Here is the sample data.

 

ID Rx1 Rx2 Rx3 Rx4 Rx5 Rx6 Rx7 Rx8 Rx9 Rx10 Rx11 Rx12 Rx13 Rx14 Rx15 Rx16 Rx17 Rx18
1 10 12 14 16 16 20 18 15 12 11 13 7 3 1 8 10 12 14
2 11 13 15 17 17 21 19 16 13 12 14 8 4 2 9 11 13 15
3 9.2 11.04 12.88 14.72 14.72 18.4 16.56 13.8 11.04 10.12 11.96 6.44 2.76 0.92 7.36 9.2 11.04 12.88
4 12.2 14.04 15.88 17.72 17.72 21.4 19.56 16.8 14.04 13.12 14.96 9.44 5.76 3.92 10.36 12.2 14.04 15.88
5 8 9.6 11.2 12.8 12.8 16 14.4 12 9.6 8.8 10.4 5.6 2.4 0.8 6.4 8 9.6 11.2
6 12 13.6 15.2 16.8 16.8 20 18.4 16 13.6 12.8 14.4 9.6 6.4 4.8 10.4 12 13.6 15.2
7 7 9 11 13 13 17 15 12 9 8 10 4 0 -2 5 7 9 11
8 9.6 10.88 12.16 13.44 13.44 16 14.72 12.8 10.88 10.24 11.52 7.68 5.12 3.84 8.32 9.6 10.88 12.16
9 8.64 9.792 10.944 12.096 12.096 14.4 13.248 11.52 9.792 9.216 10.368 6.912 4.608 3.456 7.488 8.64 9.792 10.944
10 13.64 14.792 15.944 17.096 17.096 19.4 18.248 16.52 14.792 14.216 15.368 11.912 9.608 8.456 12.488 13.64 14.792 15.944

 

Super User
Posts: 19,770

Re: SAS Rolling total or average

[ Edited ]

Assuming the indices are the months you can easily do it via an array.

data want;
set have;
array orig(18) rx1-rx18;
array avg(16) avg1-avg16;
do i=1 to 16;
avg(i)=rx(i) + rx(i+1) + rx(i+2);
end;
run;

Valued Guide
Posts: 860

Re: SAS Rolling total or average

[ Edited ]

Here's another guess, edited to add rolling total:

 


data have;
infile cards dsd;
input ID Rx1 Rx2 Rx3 Rx4 Rx5 Rx6 Rx7 Rx8 Rx9 Rx10 Rx11 Rx12 Rx13 Rx14 Rx15 Rx16 Rx17 Rx18;
cards;
1,10,12,14,16,16,20,18,15,12,11,13,7,3,1,8,10,12,14
2,11,13,15,17,17,21,19,16,13,12,14,8,4,2,9,11,13,15
3,9.2,11.04,12.88,14.72,14.72,18.4,16.56,13.8,11.04,10.12,11.96,6.44,2.76,0.92,7.36,9.2,11.04,12.88
4,12.2,14.04,15.88,17.72,17.72,21.4,19.56,16.8,14.04,13.12,14.96,9.44,5.76,3.92,10.36,12.2,14.04,15.88
5,8,9.6,11.2,12.8,12.8,16,14.4,12,9.6,8.8,10.4,5.6,2.4,0.8,6.4,8,9.6,11.2
6,12,13.6,15.2,16.8,16.8,20,18.4,16,13.6,12.8,14.4,9.6,6.4,4.8,10.4,12,13.6,15.2
7,7,9,11,13,13,17,15,12,9,8,10,4,0,-2,5,7,9,11
8,9.6,10.88,12.16,13.44,13.44,16,14.72,12.8,10.88,10.24,11.52,7.68,5.12,3.84,8.32,9.6,10.88,12.16
9,8.64,9.792,10.944,12.096,12.096,14.4,13.248,11.52,9.792,9.216,10.368,6.912,4.608,3.456,7.488,8.64,9.792,10.944
10,13.64,14.792,15.944,17.096,17.096,19.4,18.248,16.52,14.792,14.216,15.368,11.912,9.608,8.456,12.488,13.64,14.792,15.944
;

data test;
set have;
%macro avg;
%do i = 18 %to 3 %by -1;
%let n = %eval(&i - 1);
%let j = %eval(&i - 2);
avg&i = (rx&i + rx&n + rx&j)/3;
%end;
%mend;
%avg;
run;

proc transpose data=test out=tran_test;by id;

data one;
set tran_test;
by id;
where _name_ = :"R";
test1 = input(substr(_name_,3),8.);
Rolling_Total + col1;
if first.id then rolling_total = col1;
run;

data two;
set tran_test;
where _name_ = :"a";
test1 = input(substr(_name_,4),8.);
run;

proc sql;
create table want as
select a.id,a._name_ as Rx,a.col1 as Value,a.rolling_total,b.col1 as Avg
from one a left join
     two b on
a.id = b.id and
a.test1 = b.test1
order by id,input(substr(rx,3),8.);

Ask a Question
Discussion stats
  • 2 replies
  • 231 views
  • 0 likes
  • 3 in conversation