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;
That's fine, I wasn't using it 😉
Most of my code originates from somewhere else originally.
If window size is less than 5 it may be easier to just use the LAG functions. In combination with a short cut list, ie max(of age_lag:) you may be able to skip some steps.
I did also write this macro which will create the lagged variables required.
https://gist.github.com/statgeek/cb52f1a54868e959a838f2b6f3dfad20
Do you have SAS/ETS licensed?
If so look at PROC EXPAND.
That's fine, I wasn't using it 😉
Most of my code originates from somewhere else originally.
If window size is less than 5 it may be easier to just use the LAG functions. In combination with a short cut list, ie max(of age_lag:) you may be able to skip some steps.
I did also write this macro which will create the lagged variables required.
https://gist.github.com/statgeek/cb52f1a54868e959a838f2b6f3dfad20
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.