DATA Step, Macro, Functions and more

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

Reply
Frequent Learner
Posts: 1

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

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.

Super User
Posts: 10,048

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

Posted in reply to harpaljosan

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;
Super User
Posts: 7,868

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

Posted in reply to harpaljosan
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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,934

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

Posted in reply to KurtBremser

PROC RANK will do this as well.

 

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

Ask a Question
Discussion stats
  • 3 replies
  • 1416 views
  • 2 likes
  • 4 in conversation