data l;
input id anydtdte11.;
format id ddmmyy10.;
cards;
12/01/2011
10/12/2010
19/02/2011
23/08/2011
run;
data l; input id ddmmyy10.; format id ddmmyy10.; cards; 12/01/2011 10/12/2010 19/02/2011 23/08/2011 ; run; proc sort data=l out=temp nodupkey; by descending id;run; data _null_; set temp ; if _n_ eq 2 then do; putlog 'Second Largest is : ' id ddmmyy10.;stop;end; run;
Ksharp
Or as a variation:
proc sort data=l out=temp nodupkey; by descending id;run;
data _null_;
set temp (obs=2 firstobs=2);
putlog 'Second Largest is : ' id ddmmyy10.;
run;
If your data file is quite small (i.e., less than 5000 or so records) you might be able to get away with:
data l;
input id anydtdte11.;
format id ddmmyy10.;
cards;
12/01/2011
10/12/2010
19/02/2011
23/08/2011
run;
proc sql;
select id format=best12.
into :ids separated by ','
from l;
select distinct largest(2,&ids.) format=date9.
as highest
from l;
quit;
Art
I would suggest
proc sql;
select /* distinct */ id format=best12.
into :ids separated by ','
from l;
select largest(2,&ids.) format=date9.
as highest
from sashelp.class(obs=1);
quit;
Use of DISTINCT in the first SELECT depends upon how ties are to be treated.
No need for DISTINCT in the second SELECT if only one row is processed. The FROM clause can point to any table having at least one row and one column.
Here is method using hash objects
data have;
input dt ddmmyy10.;
cards;
12/01/2011
10/12/2010
19/02/2011
23/08/2011
;
run;
data _null_;
if 0 then set have;
declare hash foo(dataset:'have',ordered:'d');
declare hiter iter('foo');
foo.definekey('dt');
foo.definedata('dt');
foo.definedone();
iter.first(); iter.next();
putlog 'Second Largest is : ' dt date9.;
run;
proc sql;
create table l2 as
select distinct max(a.id) as id format=ddmmyy10.
from l as a left join (select max(id) as id from l) as b on b.id > a.id;
quit;
another way:
data l;
input id anydtdte11.;
format id ddmmyy10.;
cards;
12/01/2011
10/12/2010
19/02/2011
23/08/2011
;
run;
proc transpose data=l out=temp;
run;
data _null_;
set temp;
array x(*) col:;
call sortn(of x(*));
putlog 'Second Largest is : ' x(dim(x)-1) ddmmyy10.;
run;
Linlin
hi ... if you've already trasposed the data, how about just using the LARGEST function ...
data _null_;
set temp;
second = largest(2,of col:);
putlog 'Second Largest is : ' second ddmmyy10.;
run;
Thank you Mike!
you are right, it is more efficient using "second=largest(2,of col:)";
data l;
input id anydtdte11.;
format id ddmmyy10.;
cards;
12/01/2011
10/12/2010
19/02/2011
23/08/2011
run;
Proc Sql noprint;
select unique(id)
into :id1 - :id2
from work.l
order by id descending;
quit;
%put ID2: &id2;
data l; input id ddmmyy10.; format id ddmmyy10.; cards; 12/01/2011 10/12/2010 19/02/2011 23/08/2011 ; run; ods select extremevalues; proc univariate nextrval=2; var id; run;
Ksharp
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.