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 |
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;
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.);
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.