DATA Step, Macro, Functions and more

How to find the second highest value in given data?

Reply
Contributor
Posts: 53

How to find the second highest value in given data?

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 ?  

Super User
Posts: 19,878

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

Posted in reply to rajeshalwayswel

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. 

Super Contributor
Posts: 271

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

[ Edited ]
Posted in reply to rajeshalwayswel
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;
Trusted Advisor
Posts: 1,022

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

Posted in reply to rajeshalwayswel

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;
PROC Star
Posts: 1,760

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

[ Edited ]
Posted in reply to rajeshalwayswel


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;

 

 

Ask a Question
Discussion stats
  • 4 replies
  • 140 views
  • 0 likes
  • 5 in conversation