topic Re: 2nd highest salary of each employee in SAS Programming

2nd highest salary of each employee

adi121 — Sat, 20 Apr 2019 09:25:20 GMT

i want to find 2nd highest salary of each employee.

data sal;
input id name$ salary dt date11.;
format dt date9.;
cards;
101 nick 45000 01jan2019
101 nick 50000 01feb2019
101 nick 52000 01mar2019
101 nick 53000 01apr2019
102 mark 55000 01jan2019
102 mark 56000 01feb2019
102 mark 57000 01mar2019
102 maek 58000 01apr2019
;run;

i have done using sql

proc sql;
select max(T.salary) as sal,T.id from sal
T inner join (select max(salary) as sal,
id from sal group by id) TT on T.salary<>TT.sal and
T.id=TT.id group by T.id;
run;

Is there any more simple method in SQL .

Re: 2nd highest salary of each employee

Shmuel — Sat, 20 Apr 2019 10:10:53 GMT

Why not use base sas / data step?

data sal;
input id name$ salary dt date11.;
format dt date9.;
cards;
101 nick 45000 01jan2019
101 nick 50000 01feb2019
101 nick 52000 01mar2019
101 nick 53000 01apr2019
102 mark 55000 01jan2019
102 mark 56000 01feb2019
102 mark 57000 01mar2019
102 maek 58000 01apr2019
;run;

proc sort data=sal; by id descending salary; run;

data want;
 set sal;
      by id;
           retain flag;
           if first.id then flag=1;
           else if flag=1 then do;
               output; flag=0;
          end;
run;

Re: 2nd highest salary of each employee

Ksharp — Sat, 20 Apr 2019 10:46:54 GMT


proc univariate data=sal nextrval=2;
by id;
var salary;
run;

Re: 2nd highest salary of each employee

PeterClemmensen — Sat, 20 Apr 2019 11:15:51 GMT

You can add something like the below to @Ksharp s code to get a SAS data set with the desired data

ods output ExtremeValues=ExtremeValues(where=(HighOrder=3) keep=id High HighOrder);

Re: 2nd highest salary of each employee

yabwon — Sat, 20 Apr 2019 15:45:58 GMT

May I offer small extension to your code? It gives more flexibility.

all the best

Bart

proc sort data=sal; 
  by id descending salary; 
run;

data want;
  set sal;
  by id;

  retain flag;
  if first.id then flag=1;
              else flag = flag + 1;

  if flag=2 then /* 2 - second, 3 = third, ...*/
  do;
    output; 
    flag=.;
  end;
run;

Re: 2nd highest salary of each employee

justusjillella — Sat, 20 Apr 2019 16:32:01 GMT

Re: 2nd highest salary of each employee

ghosh — Sat, 20 Apr 2019 17:03:53 GMT

proc rank data=sal descending out=ranking ties=dense;
   by id;
	var salary;
    ranks salrank;
run;

proc print n;
  where salrank=2;
run;

Re: 2nd highest salary of each employee

KachiM — Sun, 21 Apr 2019 16:39:23 GMT

A simple solution.

proc sort data=sal; by id descending salary; run;

data need;
   do i = 1 by 1 until(last.id);
      set sal;
      by id;
      if i = 2 then output;
   end;
run;

Re: 2nd highest salary of each employee

Ksharp — Mon, 22 Apr 2019 12:50:38 GMT

What if there are two same max salary ? Like:

id salary

1 10

1 8

1 6

2 10

...........

Re: 2nd highest salary of each employee

KachiM — Mon, 22 Apr 2019 13:56:12 GMT

I didn't see the ties requirement of OP. There are many answers for that and let OP choose.

Re: 2nd highest salary of each employee

ghosh — Mon, 22 Apr 2019 20:35:35 GMT

To ensure all ties have the same rank I used the Proc rank option ties=dense. In your example data salary 10 is tied at #1 and the answer for the OP (Rank 2) will be salary of 8 for ID 1