topic SAS Rolling total or average in SAS Programming

SAS Rolling total or average

skallamp — Wed, 02 Dec 2015 18:42:26 GMT

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

Re: SAS Rolling total or average

Reeza — Wed, 02 Dec 2015 19:01:22 GMT

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;

Re: SAS Rolling total or average

Steelers_In_DC — Wed, 02 Dec 2015 21:04:27 GMT

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