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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.