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
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;
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?
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.