topic How to find the second highest value in given data? in SAS Programming

How to find the second highest value in given data?

rajeshalwayswel — Wed, 01 Nov 2017 21:24:43 GMT

data second;
input name$ number;
cards;
aravind 98
kiran 49
lahari 58
ahalya 97
amith 69
ankit 97
abhhi 70
;
run;

In data steps and proc sql ?

Re: How to find the second highest value in given data?

Reeza — Wed, 01 Nov 2017 21:34:29 GMT

proc sort data=have; by descending number;

run;

data want;

set have (obs=2 firstobs=2);

run;

Or see the SAS PROC UNIVARIATE/PROC MEANS on isolating the top X values or the Xth value. There's an example in the documentation for each of the procedures.

Re: How to find the second highest value in given data?

SAS_inquisitive — Thu, 02 Nov 2017 01:46:06 GMT

data second;
input name$ number;
cards;
aravind 98
kiran 49
lahari 58
ahalya 97
amith 69
ankit 97
abhhi 70
;
run;

proc sql print;
    
   create table tmp1 as
	select name,number
	  from second
	order by number desc;

	create table tmp2 as
	   select name, number, monotonic() as rank
	    from tmp1
	    where calculated rank = 2;

	create table want as
	   select *
	     from tmp1 
		   where number in (select number from tmp2);
	 
quit;

Re: How to find the second highest value in given data?

mkeintz — Wed, 01 Nov 2017 22:34:51 GMT

With each incoming observation, you can just maintain a record of the 1st and 2nd maximum values and their corresponding observation numbers. Then, at the end of the dataset, just reread the observation number (_p2 below) that corresponds to the 2nd largest value (_max2):

data second;
input name$ number;
cards;
aravind 98
kiran 49
lahari 58
ahalya 97
amith 69
ankit 97
abhhi 70
;
run;

data want (drop=_:);
  set second end=eod;
  retain _max1 _max2  _p1 _p2 .;

  if number>_max1 then do;
    _max2=_max1;  _p2=_p1;
    _max1=number; _p1=_n_;
  end;
  else if number>_max2 then do;
    _max2=number; _p2=_n_;
  end;

  if eod ;
  set second point=_p2;
run;

Re: How to find the second highest value in given data?

ChrisNZ — Wed, 01 Nov 2017 23:16:41 GMT

Like this?


proc sql outobs=1; 
  create table WANT1 as 
  select * 
  from HAVE 
  having NUMBER ne max(NUMBER) 
  order by NUMBER desc ;
quit; 

data WANT2;
  if 0 then set HAVE;
  dcl hash HT(dataset:'HAVE', ordered:'descending');
  HT.definekey('NUMBER');
  HT.definedata('NAME','NUMBER');
  HT.definedone();
  dcl hiter iter('HT');
  RC=iter.first();
  RC=iter.next();
run;

Re: How to find the second highest value in given data?

subhroster — Tue, 12 Feb 2019 10:31:43 GMT

proc transpose data= second out=trans_second;

run;

data max;

set trans_second;

max=largest(2,of col1-col7);

run;

Re: How to find the second highest value in given data?

Patrick — Tue, 12 Feb 2019 12:07:13 GMT

@rajeshalwayswel

Or Proc Rank which lets you define how to deal with ties.

proc rank
    data=second
    ties=dense descending
    out=want(where=(number_rank=2))
    ;
  var number;
  ranks number_rank;
run;

Re: How to find the second highest value in given data?

Recep — Tue, 12 May 2020 22:27:30 GMT

Brilliant!

Re: How to find the second highest value in given data?

ketan_korde — Tue, 18 Oct 2022 14:39:10 GMT

Can you please explain what is happening in table want? Thank You.