BookmarkSubscribeRSS Feed
sas_Forum
Calcite | Level 5

data l;

input id anydtdte11.;

format id ddmmyy10.;

cards;

12/01/2011

10/12/2010

19/02/2011

23/08/2011

run;

11 REPLIES 11
Ksharp
Super User
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

Patrick
Opal | Level 21

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;

art297
Opal | Level 21

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

Howles
Quartz | Level 8

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.

FriedEgg
SAS Employee

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;

TD
Calcite | Level 5 TD
Calcite | Level 5

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;

Linlin
Lapis Lazuli | Level 10

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

MikeZdeb
Rhodochrosite | Level 12

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;

Linlin
Lapis Lazuli | Level 10

Thank you Mike!

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

Jay_OAG
Calcite | Level 5

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;

Ksharp
Super User
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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 8374 views
  • 1 like
  • 10 in conversation