topic Re: Nth maximum value with same values using datastep in SAS Programming

Nth maximum value with same values using datastep

BrahmanandaRao — Sat, 19 Aug 2023 11:24:40 GMT

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

Re: Nth maximum value with same values using datastep

Kurt_Bremser — Sun, 20 Aug 2023 07:54:19 GMT

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

mkeintz — Sat, 19 Aug 2023 23:49:04 GMT

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;

Re: Nth maximum value with same values using datastep

mkeintz — Mon, 21 Aug 2023 03:32:35 GMT

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;

Re: Nth maximum value with same values using datastep

BrahmanandaRao — Mon, 21 Aug 2023 04:59:47 GMT




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

Re: Nth maximum value with same values using datastep

BrahmanandaRao — Mon, 21 Aug 2023 05:02:33 GMT

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

Re: Nth maximum value with same values using datastep

mkeintz — Sun, 27 Aug 2023 01:54:14 GMT

@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?