BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
animesh123
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

14 REPLIES 14
PeterClemmensen
Tourmaline | Level 20

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

animesh123
Obsidian | Level 7
if there is a scenario where
- Required both 2 obs
- Also 1 OBS
PeterClemmensen
Tourmaline | Level 20

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
Obsidian | Level 7
For 2 Obs the output :-
3 AE 50000
5 AF 50000

And For 1 Obs the output:
AF 50000
PeterClemmensen
Tourmaline | Level 20

50000 is not the third highest salary?

PeterClemmensen
Tourmaline | Level 20

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
Obsidian | Level 7
Sorry my bad , If I need to do the same using SQL
6 AK 15000
7 AZ 15000
PeterClemmensen
Tourmaline | Level 20

Why does it have to be SQL?

animesh123
Obsidian | Level 7
Its just a question if I need to find out the same using PROC SQL / SQL
Ksharp
Super User
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
Obsidian | Level 7
Hey Thanks @Ksharp . So the Output of the query will be
6 AK 15000
7 AZ 15000
animesh123
Obsidian | Level 7
Also please explain this
having calculated n=2
Ksharp
Super User
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
Obsidian | Level 7
Thanks @Ksharp I got it

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 14 replies
  • 2871 views
  • 0 likes
  • 3 in conversation