☑ This topic is solved.
Need further help from the community? Please
sign in and ask a new question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 12-07-2022 05:41 AM
(3981 views)
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
14 REPLIES 14
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
What if 2 obs have the third highest salary? Do you want both obs?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
if there is a scenario where
- Required both 2 obs
- Also 1 OBS
- Required both 2 obs
- Also 1 OBS
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
For 2 Obs the output :-
3 AE 50000
5 AF 50000
And For 1 Obs the output:
AF 50000
3 AE 50000
5 AF 50000
And For 1 Obs the output:
AF 50000
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
50000 is not the third highest salary?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Sorry my bad , If I need to do the same using SQL
6 AK 15000
7 AZ 15000
6 AK 15000
7 AZ 15000
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Why does it have to be SQL?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Its just a question if I need to find out the same using PROC SQL / SQL
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Also please explain this
having calculated n=2
having calculated n=2
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
So the current obs/value is the 3rd highest value.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks @Ksharp I got it