How to find the Nth largest salary from a sas dataset using Proc SQL?
EMPNO Salary empcode
111 4000 A
112 6000 A
114 2000 A
115 8000 A
223 2000 B
226 1000 B
228 3000 B
300 500 C
333 700 C
345 300 C
356 200 C
320 700 C
i want to find out the 3rd largest salary group by empcode using Proc Sql.
I really suggest you to use data step , NOT Sql.
data have;
input EMPNO Salary empcode $;
cards;
111 4000 A
112 6000 A
114 2000 A
115 8000 A
223 2000 B
226 1000 B
228 3000 B
300 500 C
333 700 C
345 300 C
356 200 C
320 700 C
;
run;
proc sql;
create table want as
select *,
(select count(*) from
(select distinct Salary,empcode from have) as b
where b.empcode=a.empcode and b.Salary gt a.Salary) as n
from have as a
where calculated n=2;
quit;
proc sort data=have;
by descending salary;
run;
data want;
set have;
if _n_ = number_you_want then do;
output;
stop;
end;
run;
Absolutely no need for SQL.
PROC RANK will do this as well.
I agree with the others, it is really pointless to insist on doing this in PROC SQL
this program will not work if there are multiple salaries with the same value.
The question was to find a single value, which my code does. See Maxim 42 😉
But the data step can easily be adapted to deal with groups:
data want;
set have;
by salary;
if first.salary then n + 1;
if n > number_you_want then stop;
if n = number_you_want;
run;
@sas_guru wrote:
this program will not work if there are multiple salaries with the same value.
As I said above, PROC RANK is the tool to use, it will find multiple salary values with the same values.
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.