Sunday 18 February 2018

highest nth value in proc 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;

No comments:

Post a Comment