## previous max and succeeding max of n sample

Solved
Regular Contributor
Posts: 210

# previous max and succeeding max of n sample

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;```

Accepted Solutions
Solution
‎05-14-2017 04:17 PM
Super User
Posts: 24,003

## Re: previous max and succeeding max of n sample

Posted in reply to csetzkorn

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.

All Replies
Super User
Posts: 24,003

## Re: previous max and succeeding max of n sample

Posted in reply to csetzkorn

Do you have SAS/ETS licensed?

If so look at PROC EXPAND.

Regular Contributor
Posts: 210

## Re: previous max and succeeding max of n sample

No alas not. Btw I based this code on some moving average code, which I believe you created (-:
Solution
‎05-14-2017 04:17 PM
Super User
Posts: 24,003

## Re: previous max and succeeding max of n sample

Posted in reply to csetzkorn

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
• 3 replies
• 182 views
• 1 like
• 2 in conversation