BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
BrahmanandaRao
Lapis Lazuli | Level 10

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
Kurt_Bremser
Super User

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.

View solution in original post

6 REPLIES 6
Kurt_Bremser
Super User

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.

BrahmanandaRao
Lapis Lazuli | Level 10



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,

Kurt  for your solution 

mkeintz
PROC Star

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>=age2 then h.add();
  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

--------------------------
mkeintz
PROC Star

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

--------------------------
BrahmanandaRao
Lapis Lazuli | Level 10

Thank You,

Mkeintz for your solution 

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

mkeintz
PROC Star

@BrahmanandaRao wrote:

Thank You,

Mkeintz for your solution 

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

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 918 views
  • 0 likes
  • 3 in conversation