topic Re: How to find the Nth largest salary from a sas dataset using Proc SQL in SAS Programming

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

harpaljosan — Sat, 19 Sep 2015 10:04:20 GMT

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.

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

Ksharp — Sat, 19 Sep 2015 10:55:46 GMT

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;

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

Kurt_Bremser — Sat, 19 Sep 2015 11:18:29 GMT

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.

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

PaigeMiller — Sat, 19 Sep 2015 12:25:22 GMT

PROC RANK will do this as well.

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

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

sas_guru — Mon, 01 Aug 2022 05:09:06 GMT

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

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

Kurt_Bremser — Mon, 01 Aug 2022 05:18:11 GMT

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

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

Kurt_Bremser — Mon, 01 Aug 2022 05:36:55 GMT

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;

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

PaigeMiller — Mon, 01 Aug 2022 12:18:24 GMT

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