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-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
  • 7 replies
  • 1295 views
  • 7 likes
  • 6 in conversation