## Nth maximum value with same values using datastep

Hi Guys,

I am trying Nth max value using datastep if same nth maxvalues in  row a dataset

``````/*Using proc rank procedure nth maxvalues in a row */

proc rank
data=sashelp.class
ties=dense descending
out=want(where=(number_rank=2))
;
var age;
ranks number_rank;
run;

data rank;
set sashelp.class;
run;

proc sort data=rank;
by descending age;
run;

data ranking;
set rank;
by  descending age ;
retain ranks;
if first.age then ranks+1;
proc print noobs;
run;

data maxcount ;
set ranking (obs=5);
if _n_ =2 then output;
proc print noobs;
run;

proc rank
data=sashelp.class
ties=dense descending
out=want(where=(number_rank=2))
;
var age;
ranks number_rank;
run;

``````

I want required output

 Name Sex Age Height Weight number_rank Janet F 15 62.5 112.5 2 Mary F 15 66.5 112 2 Ronald M 15 67 133 2 William M 15 66.5 112 2

1 ACCEPTED SOLUTION

Accepted Solutions

## Re: Nth maximum value with same values using datastep

For efficiency, combine the first two steps:

``````proc sort
data=sashelp.class
out=rank
;
by descending age;
run;
``````

Then, define the ranks:

``````data want;
set rank;
by descending age;
rank + (first.age); * SUM statement implies RETAIN;
run;``````

I leave it as a simple exercise to filter for rank = 2 in the second step.

Edit: added the DESCENDING keyword in the DATA step.

6 REPLIES 6

## Re: Nth maximum value with same values using datastep

For efficiency, combine the first two steps:

``````proc sort
data=sashelp.class
out=rank
;
by descending age;
run;
``````

Then, define the ranks:

``````data want;
set rank;
by descending age;
rank + (first.age); * SUM statement implies RETAIN;
run;``````

I leave it as a simple exercise to filter for rank = 2 in the second step.

Edit: added the DESCENDING keyword in the DATA step.

## Re: Nth maximum value with same values using datastep

``````proc sort
data=sashelp.class
out=rank
;
by descending age;
run;

data want;
set rank ;
by descending age;
rank + (first.age); * SUM statement implies RETAIN;
if rank=2 ; /*fIlter Nth rank using if statement Note  where statement cannot work here because assingnment variable rank only work with IF statement*/
proc print noobs;
run``````

Thank you,

## Re: Nth maximum value with same values using datastep

In this case you merely need to keep a running record of the top 2 values of AGE as you read sashelp.class.  Every time an obs is encountered that matches the Nth largest age (2nd largest), stick it in a hash object.

When you reach the end of sashelp.class, you know the Nth largest age value, and you can use that to output data from the hash object, filtering on age=Nth highest value.  Given the hash object is declared to be sorted by the same var (name) as sashelp.class, the resulting dataset has the original data order:

``````data _null_;
set sashelp.class end=end_of_class;
retain age1 age2 ;

if _n_=1 then do;
declare hash h (dataset:'sashelp.class (obs=0)',ordered:'A');
h.definekey('name');
h.definedata(all:'Y');
h.definedone();
end;

if age>age1 then do; age2=age1; age1=age; end; else
if age>age2 then     age2=age;

if end_of_class then h.output(dataset:cats('want (where=(age=',age2,'))'));

run;``````

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

## Re: Nth maximum value with same values using datastep

If you're willing to read the dataset twice in a single data step, then the simplest code would be:

``````data want;
set sashelp.class (in=first_pass)  sashelp.class (in=second_pass);
array _age{2} _temporary_;
if first_pass=1 and age>_age{1} then do;
_age{1}=age;
call sortn(of _age{*});   /* Results in ascending sort of _age{1} and _age{2} */
end;
if second_pass and age=_age{1};
run;``````
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

## Re: Nth maximum value with same values using datastep

Thank You,

in your tricky code how to filter Nth max values  suppose i want 5 max value where i have to change in this code

## Re: Nth maximum value with same values using datastep

@Anandkvn wrote:

Thank You,

in your tricky code how to filter Nth max values  suppose i want 5 max value where i have to change in this code

Do you want the 5th largest age, or all observations with the 5th largest or larger?

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Discussion stats
• 6 replies
• 223 views
• 0 likes
• 3 in conversation