Find 3rd highest salary

[animesh123]

Find 3RD highest salary for the below scnario

ID  name  Salary

1       AM  1000

2       AD   40000

3       AE   50000

5      AF     50000

6     AK    15000

7    AS     2500

[PeterClemmensen]

What if 2 obs have the third highest salary? Do you want both obs?

[animesh123]

if there is a scenario where
- Required both 2 obs
- Also 1 OBS

[PeterClemmensen]

Please post your desired result given this input data.

data have;
input ID name $ Salary;
datalines;
1 AM 1000  
2 AD 40000
3 AE 50000
5 AF 50000
6 AK 15000
7 AZ 15000
8 AS 2500
;

[animesh123]

For 2 Obs the output :-
3 AE 50000
5 AF 50000

And For 1 Obs the output:
AF 50000

[PeterClemmensen]

50000 is not the third highest salary?

[PeterClemmensen]

Anyways, use Proc Rank and apply the ties option to your liking.

data have;
input ID name $ Salary;
datalines;
1 AM 1000  
2 AD 40000 
3 AE 50000 
5 AF 50000 
6 AK 15000 
7 AZ 15000 
8 AS 2500  
;

proc rank data = have out = rank descending ties = dense;
   var Salary;
   ranks r;
run;

[animesh123]

Sorry my bad , If I need to do the same using SQL
6 AK 15000
7 AZ 15000

[PeterClemmensen]

Why does it have to be SQL?

[animesh123]

Its just a question if I need to find out the same using PROC SQL / SQL

[Ksharp · Accepted solution]

data have;
input ID name $ Salary;
datalines;
1 AM 1000  
2 AD 40000 
3 AE 50000 
5 AF 50000 
6 AK 15000 
7 AZ 15000 
8 AS 2500  
;
proc sql;
select a.id,a.name,a.Salary,count(distinct b.salary) as n
 from have as a,have as b
  where a.salary<b.salary
   group by a.id,a.name,a.Salary
    having calculated n=2
    order by 1
;
quit;

[animesh123]

Hey Thanks @Ksharp . So the Output of the query will be
6 AK 15000
7 AZ 15000

[animesh123]

Also please explain this
having calculated n=2

[Ksharp]

It means there are two value greater than the current obs/value (6 AK 15000).
So the current obs/value is the 3rd highest value.

[animesh123]

Thanks @Ksharp I got it