DATA Step, Macro, Functions and more

To find the second Highest Date

Reply
Frequent Contributor
Posts: 140

To find the second Highest Date

data l;

input id anydtdte11.;

format id ddmmyy10.;

cards;

12/01/2011

10/12/2010

19/02/2011

23/08/2011

run;

Super User
Posts: 9,673

To find the second Highest Date

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

Respected Advisor
Posts: 3,887

To find the second Highest Date

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;

PROC Star
Posts: 7,360

To find the second Highest Date

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

Regular Contributor
Posts: 184

To find the second Highest Date

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.

Trusted Advisor
Posts: 1,300

To find the second Highest Date

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;

Occasional Contributor TD
Occasional Contributor
Posts: 13

To find the second Highest Date

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;

Super Contributor
Posts: 1,636

Re: To find the second Highest Date

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

Valued Guide
Posts: 765

Re: To find the second Highest Date

hi ... if you've already trasposed the data, how about just using the LARGEST function ...

data _null_;

set temp;

second = largest(2,of colSmiley Happy;

putlog 'Second Largest is : ' second ddmmyy10.;

run;

Super Contributor
Posts: 1,636

Re: To find the second Highest Date

Thank you Mike!

you are right, it is more efficient using "second=largest(2,of colSmiley Happy";

Occasional Contributor
Posts: 14

To find the second Highest Date

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;

Super User
Posts: 9,673

To find the second Highest Date

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

Ask a Question
Discussion stats
  • 11 replies
  • 4107 views
  • 1 like
  • 10 in conversation