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 ?
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.
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;
proc transpose data= second out=trans_second; run; data max; set trans_second; max=largest(2,of col1-col7); run;
Brilliant!
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.