BookmarkSubscribeRSS Feed
skallamp
Obsidian | Level 7

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

 

2 REPLIES 2
Reeza
Super User

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;

Steelers_In_DC
Barite | Level 11

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.);

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 954 views
  • 0 likes
  • 3 in conversation