How to find the second highest value in given data?

[rajeshalwayswel]

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 ?  

[Reeza]

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. 

[SAS_inquisitive]

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;

[subhroster]

proc transpose data= second out=trans_second;

run;

data max;

set trans_second;

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

run;

[Recep]

Brilliant!

[ketan_korde]

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

[mkeintz]

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;

[ChrisNZ]

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;

[Patrick]

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