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