previous max and succeeding max of n sample

[csetzkorn]

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;

[Reeza]

Do you have SAS/ETS licensed?

If so look at PROC EXPAND.

[csetzkorn]

No alas not. Btw I based this code on some moving average code, which I believe you created (-:

[Reeza · Accepted solution]

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