I came up with the code below, which intends to create 2 columns: MaxPrev and MaxSucc which contain the maximum value of the previous and succeeding 3 sample (based on DateId). There are 2 columns which group the samples: ID1 ID2. The code seems to work fine but maybe it can be improved. Any suggestions would be very much appreciated. Thanks. DATA work.have;
INPUT ID1 ID2 DateId SomeValue;
DATALINES;
1 2 1 10
1 2 2 10
1 2 3 10
1 2 4 10
1 2 5 20
1 2 6 10
1 2 7 10
1 2 8 10
1 2 9 10
1 2 10 10
1 2 11 10
1 2 12 10
1 2 13 10
1 2 14 10
1 2 15 10
1 2 16 10
1 2 17 10
1 2 18 10
1 2 19 10
1 2 20 10
1 2 21 10
1 2 22 10
1 2 23 10
1 2 24 30
1 2 25 10
1 2 26 10
1 2 27 10
1 2 28 10
1 2 29 10
1 2 30 10
1 2 31 10
1 2 32 10
1 2 33 10
run;
%let winsize=4;
proc sort data = have;
by ID1 ID2 DateId ;
run;
data have;
set have;
by ID1 ID2;
retain pre1-pre&winsize.;
array pre(&winsize.);
if first.ID1 then
do;
call missing(of pre(*));
count=0;
end;
count+1;
index=mod(count, &winsize.)+1;
pre(index)=SomeValue;
if count>=&winsize. then
MaxPrev=max(of pre(*));
drop count index pre1-pre&winsize.;
run;
proc sort data = have;
by ID1 ID2 descending DateId ;
run;
data have;
set have;
by ID1 ID2;
retain pre1-pre&winsize.;
array pre(&winsize.);
if first.ID1 then
do;
call missing(of pre(*));
count=0;
end;
count+1;
index=mod(count, &winsize.)+1;
pre(index)=SomeValue;
if count>=&winsize. then
MaxSucc=max(of pre(*));
drop count index pre1-pre&winsize.;
run;
proc sort data = have;
by ID1 ID2 DateId ;
run;
... View more
