topic Re: Leave 3 best results in table in SAS Programming

Leave 3 best results in table

Efka — Fri, 03 May 2013 17:39:23 GMT

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.

Re: Leave 3 best results in table

jwsquillace — Fri, 03 May 2013 17:43:42 GMT

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;

Re: Leave 3 best results in table

PGStats — Fri, 03 May 2013 18:06:25 GMT

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;

Re: Leave 3 best results in table

DBailey — Fri, 03 May 2013 19:39:45 GMT

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

Re: Leave 3 best results in table

Haikuo — Fri, 03 May 2013 19:43:29 GMT

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

Re: Leave 3 best results in table

Ankitsas — Sat, 04 May 2013 14:35:12 GMT

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

Re: Leave 3 best results in table

Efka — Sun, 05 May 2013 11:09:17 GMT

thank You all!

Re: Leave 3 best results in table

Haikuo — Sun, 05 May 2013 22:25:22 GMT

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