DATA Step, Macro, Functions and more

previous max and succeeding max of n sample

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 110
Accepted Solution

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: 19,815

Re: previous max and succeeding max of n sample

Posted in reply to csetzkorn

That's fine, I wasn't using it Smiley Wink

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_lagSmiley Happy 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

 

 

View solution in original post


All Replies
Super User
Posts: 19,815

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.

Frequent Contributor
Posts: 110

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: 19,815

Re: previous max and succeeding max of n sample

Posted in reply to csetzkorn

That's fine, I wasn't using it Smiley Wink

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_lagSmiley Happy 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

 

 

☑ This topic is solved.

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

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