topic To find the second Highest Date in SAS Programming

To find the second Highest Date

sas_Forum — Tue, 10 Jan 2012 07:08:37 GMT

data l;

input id anydtdte11.;

format id ddmmyy10.;

cards;

12/01/2011

10/12/2010

19/02/2011

23/08/2011

run;

To find the second Highest Date

Ksharp — Tue, 10 Jan 2012 07:57:19 GMT

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

To find the second Highest Date

Patrick — Tue, 10 Jan 2012 09:48:43 GMT

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;

To find the second Highest Date

art297 — Tue, 10 Jan 2012 14:13:41 GMT

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

To find the second Highest Date

FriedEgg — Tue, 10 Jan 2012 14:31:47 GMT

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;

To find the second Highest Date

TD — Tue, 10 Jan 2012 14:42:20 GMT

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;

Re: To find the second Highest Date

Linlin — Tue, 10 Jan 2012 14:51:44 GMT

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

Re: To find the second Highest Date

MikeZdeb — Wed, 11 Jan 2012 04:21:13 GMT

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;

Re: To find the second Highest Date

Linlin — Wed, 11 Jan 2012 13:28:22 GMT

Thank you Mike!

you are right, it is more efficient using "second=largest(2,of col:)";

To find the second Highest Date

Jay_OAG — Wed, 11 Jan 2012 20:50:59 GMT

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;

To find the second Highest Date

Howles — Thu, 12 Jan 2012 02:35:32 GMT

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.

To find the second Highest Date

Ksharp — Thu, 12 Jan 2012 05:10:21 GMT

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