Solved
Contributor
Posts: 44

# how get top 3 values

Hi friends

How to get top 3 values

i have  data set like this

data x;

infile datalines;

input name\$ sal;

datalines;

a 2500

b 3600

c 1200

d 3600

e 1500

f 2000

x 1000

;

run;

Then    i want data like this

Name           sal

b                  3600

d                  3600

a                  2500

f                   2000

plz help me

Accepted Solutions
Solution
‎06-17-2015 06:43 AM
Super User
Posts: 10,280

## Re: how get top 3  values

OK, so we must expand that a little.

First step stays the same:

proc sort data=x;

by descending sal;

run;

Now let's find the third value from the top:

data _null_;

retain counter 0;

set x;

by descending sal;

if first.sal then counter + 1;

if counter = 3 then do;

call symput('third_val',put(sal,best.));

stop;

end;

run;

Now iterate through x until the treshold is reached:

data want;

set x;

if sal < &third_val then stop;

run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Super User
Posts: 10,280

## Re: how get top 3 values

proc sort data=x;

by descending sal;

run;

data want;

set x (obs=3);

run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 44

## Re: how get top 3  values

this way get only 3 rows.

then i want top 3 sal  values

Solution
‎06-17-2015 06:43 AM
Super User
Posts: 10,280

## Re: how get top 3  values

OK, so we must expand that a little.

First step stays the same:

proc sort data=x;

by descending sal;

run;

Now let's find the third value from the top:

data _null_;

retain counter 0;

set x;

by descending sal;

if first.sal then counter + 1;

if counter = 3 then do;

call symput('third_val',put(sal,best.));

stop;

end;

run;

Now iterate through x until the treshold is reached:

data want;

set x;

if sal < &third_val then stop;

run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 44

## Re: how get top 3  values

Thank you sir,

it is working .

plz send code in proc sql;

Super User
Posts: 10,280

## Re: how get top 3  values

plz send code in proc sql;

Why? You got a working, easily understandable solution already.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Valued Guide
Posts: 864

## Re: how get top 3  values

data have;

infile datalines;

input name\$ sal;

datalines;

a 2500

b 3600

c 1200

d 3600

e 1500

f 2000

x 1000

;

run;

proc sort data=have;by descending sal;

proc sql inobs=3;

create table want as

select *

from have;

Contributor
Posts: 44

## Re: how get top 3  values

Hi sir,

this way get only 3 rows.

then i want top 3 sal  values

Valued Guide
Posts: 864

## Re: how get top 3  values

I thought I would be able to do this in one step and tried but it didn't work the way I was thinking, so here's a solution using proc sql;

data have;

infile datalines;

input name\$ sal;

datalines;

a 2500

b 3600

c 1200

d 3600

e 1500

f 2000

x 1000

a 2500

b 3600

c 1200

d 3600

e 1500

f 2000

x 1000

;

run;

proc sql outobs=3;

create table prep as

select distinct sal

from have

order by sal desc;

proc sql;

create table want as

select distinct a.*

from have a inner join

prep b on

a.sal = b.sal

order by sal desc;

Contributor
Posts: 44