2nd highest salary of each employee

[adi121]

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 . 

[Shmuel]

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;    

[yabwon]

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;    

[Ksharp]

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

[PeterClemmensen]

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);

[ghosh · Accepted solution]

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

proc print n;
  where salrank=2;
run;

[KachiM]

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;

[Ksharp]

What if there are two same max salary ? Like:

id salary

1   10

1   10

1     8

1     6

2     10

...........

[KachiM]

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

[ghosh]

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