Leave 3 best results in table

Solved
Occasional Contributor
Posts: 9

Leave 3 best results in table

Hello,

I have a table (name 'Name') with data:

Name     Age

Tom        15

Garry       14

Susan     14

Paul        13

Shaun     12

So now I have to leave three best results and delete all the rest rows which gives me this output:

Name     Age

Tom        15

Garry       14

Susan     14

How is possible to do this? Examples would be better,

Thank You

p.s. I tried to solve this with obs and I couldn't , then tried with loops but I am a bit confused with them cuz I am rookie in SAS,

Thank You again.

Accepted Solutions
Solution
‎05-03-2013 02:06 PM
Posts: 5,521

Re: Leave 3 best results in table

Or...

data test;

input  Name \$ Age ;
datalines;
Tom        15
Garry       14
Susan     14
Paul        13
Shaun     12
;

proc sql outobs=3;
create table result as
select * from test

order by age desc;
quit;

PG

PG

All Replies
SAS Employee
Posts: 26

Re: Leave 3 best results in table

data test;
infile datalines truncover;
input  Name \$     Age : ;
datalines;
Tom        15
Garry       14
Susan     14
Paul        13
Shaun     12
;

proc sort data=test;
by descending age;

data result;
set test (obs=3);
run;

Solution
‎05-03-2013 02:06 PM
Posts: 5,521

Re: Leave 3 best results in table

Or...

data test;

input  Name \$ Age ;
datalines;
Tom        15
Garry       14
Susan     14
Paul        13
Shaun     12
;

proc sql outobs=3;
create table result as
select * from test

order by age desc;
quit;

PG

PG
Super Contributor
Posts: 578

Re: Leave 3 best results in table

What do you want to happen in the case that there is a tie?  For example,

Tom        15

Garry       14

Susan     14

Fred      14

Paul        13

Shaun     12

Posts: 3,167

Re: Leave 3 best results in table

Or if you don't mind getting the result in the single row / record:

data test;

infile datalines truncover;

input Name \$ Age : ;

datalines;

Tom 15

Garry 14

Susan 14

Paul 13

Shaun 12

;

proc means data=test noprint nway;

var age;

output out=top3list (drop= _

idgroup( max(age) out[3] (name age)= );

run;

Haikuo

Occasional Contributor
Posts: 14

Re: Leave 3 best results in table

Or, you can use the below code -

data test;

input  Name \$ Age ;
datalines;
Tom        15
Garry       14
Susan     14
Paul        13
Shaun     12
;

proc sort data=test;

by descending age;

run;

data name;

set test;

if _n_ <=3;

run;

---Ankit

Posts: 3,167

Re: Leave 3 best results in table

,

FWIW, while your code delivers the top 3 obs, it will still read through the whole 'test' table. It would take a toll on your efficiency if the 'test' is big (billion records in heath care or financial data) or fat (seen 100k variables in some Microarray data) or both . I would recommend the following tweak:

data name;

set test;

if _n_>3 then stop;

run;

Haikuo

Occasional Contributor
Posts: 9

Re: Leave 3 best results in table

thank You all!

🔒 This topic is solved and locked.