Learning SAS? Welcome to the exclusive online community for all SAS learners.

how get top 3 values

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

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: 6,936

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

View solution in original post


All Replies
Super User
Posts: 6,936

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
Contributor
Posts: 40

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: 6,936

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
Contributor
Posts: 40

Re: how get top 3  values

Thank you sir,

it is working .

plz send code in proc sql;

Super User
Posts: 6,936

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
Valued Guide
Posts: 858

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: 40

Re: how get top 3  values

Hi sir,

this way get only 3 rows.

then i want top 3 sal  values

Valued Guide
Posts: 858

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: 40

Re: how get top 3  values

Thanks for every one.

☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 579 views
  • 0 likes
  • 3 in conversation