Contributor
Posts: 70

# 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: 21,926

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

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: 272

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

[ Edited ]
``````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;``````
Posts: 1,215

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

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: 2,025

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

[ Edited ]

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

Discussion stats
• 4 replies
• 671 views
• 0 likes
• 5 in conversation