SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Efka
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

7 REPLIES 7
jwsquillace
SAS Employee

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;

PGStats
Opal | Level 21

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
DBailey
Lapis Lazuli | Level 10

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

Haikuo
Onyx | Level 15

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

Ankitsas
Calcite | Level 5

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

Haikuo
Onyx | Level 15

,

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

Efka
Calcite | Level 5

thank You all!

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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