How to find the Nth largest salary from a sas dataset using Proc SQL

[harpaljosan]

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.

[Ksharp]

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;

[Kurt_Bremser]

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.

[PaigeMiller]

PROC RANK will do this as well.

I agree with the others, it is really pointless to insist on doing this in PROC SQL

[sas_guru]

this program will not work if there are multiple salaries with the same value.

[Kurt_Bremser]

The question was to find a single value, which my code does. See Maxim 42 😉

[Kurt_Bremser]

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;

[PaigeMiller]

---

@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.