DATA Step, Macro, Functions and more

Leave 3 best results in table

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

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
Respected Advisor
Posts: 4,649

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

View solution in original post


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
Respected Advisor
Posts: 4,649

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

Respected Advisor
Posts: 3,124

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= _Smiley Happy

  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

Respected Advisor
Posts: 3,124

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 300 views
  • 7 likes
  • 6 in conversation