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 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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
  • 5 replies
  • 1362 views
  • 0 likes
  • 4 in conversation