New SAS User

Completely new to SAS or trying something new with SAS? Post here for help getting started.
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-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

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
  • 3982 views
  • 0 likes
  • 3 in conversation