BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vandhan
Fluorite | Level 6


data have;
input id Salary;
cards;
101 1000
102 2000
103 1000
104 3000
105 3000
106 4000

107 2000
;

 

wanted to find id and salary which is 3rd max value using sql??

in this case result should be 102, 107 with 2000 salary which is 3rd maximum

1 ACCEPTED SOLUTION

Accepted Solutions
BMiller
Obsidian | Level 7

 

I can do it in 2 using a macro assignment:

 

proc sql;
select distinct salary
into :top1 - :top3
from have
order by salary desc
;
quit;

proc sql;
select * from have
where salary = &top3.
;
quit;

View solution in original post

5 REPLIES 5
LinusH
Tourmaline | Level 20

Well, you can't exactly. You could use the undocumented/unsupported function monotonic() (gives you record no) which could be applied after a sort.

But why are you fixed with SQL?

Data never sleeps
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Post an example of your exact data, as per the duplicate post, there is more than one variable.  I would suggest a datastep, where you have an array of 3, each one containing the max e.g:

data want;
  set have;
  array results{6,3} 8.;
  retain results1-results3;
  array items{6} your_variable1-your_variable6;
  do i=1 to 6;
    if items{i} > results{i,1} then results{i,1}=items{i};
    else if items{i} > results{i,2} then results{i,2}=items{i};
    else if items{i} > results{i,3} then results{i,3}=items{i};
  end;
run;
  

You will then have a 6 * 3 array of variables which contain varnumber (1-6) of the top 3 results.  You can use this how you wish, maybe by a second datastep (or set command) which outputs when the value=the corresponding array element.  

BMiller
Obsidian | Level 7
1 sql step or can this be processed in a couple of steps?
vandhan
Fluorite | Level 6
One or many. 
BMiller
Obsidian | Level 7

 

I can do it in 2 using a macro assignment:

 

proc sql;
select distinct salary
into :top1 - :top3
from have
order by salary desc
;
quit;

proc sql;
select * from have
where salary = &top3.
;
quit;

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
  • 5 replies
  • 934 views
  • 0 likes
  • 4 in conversation