topic Re: Find 3rd highest salary in New SAS User

Find 3rd highest salary

animesh123 — Wed, 07 Dec 2022 10:41:06 GMT

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

Re: Find 3rd highest salary

PeterClemmensen — Wed, 07 Dec 2022 10:45:30 GMT

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

Re: Find 3rd highest salary

animesh123 — Wed, 07 Dec 2022 10:50:35 GMT

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

Re: Find 3rd highest salary

PeterClemmensen — Wed, 07 Dec 2022 10:53:32 GMT

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
;

Re: Find 3rd highest salary

PeterClemmensen — Wed, 07 Dec 2022 11:03:13 GMT

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;

Re: Find 3rd highest salary

animesh123 — Wed, 07 Dec 2022 11:03:14 GMT

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

And For 1 Obs the output:
AF 50000

Re: Find 3rd highest salary

PeterClemmensen — Wed, 07 Dec 2022 11:04:11 GMT

50000 is not the third highest salary?

Re: Find 3rd highest salary

animesh123 — Wed, 07 Dec 2022 11:07:01 GMT

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

Re: Find 3rd highest salary

PeterClemmensen — Wed, 07 Dec 2022 11:11:48 GMT

Why does it have to be SQL?

Re: Find 3rd highest salary

animesh123 — Wed, 07 Dec 2022 11:22:06 GMT

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

Re: Find 3rd highest salary

Ksharp — Wed, 07 Dec 2022 12:03:26 GMT

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;

Re: Find 3rd highest salary

animesh123 — Wed, 07 Dec 2022 12:59:38 GMT

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

Re: Find 3rd highest salary

animesh123 — Wed, 07 Dec 2022 13:01:20 GMT

Also please explain this
having calculated n=2

Re: Find 3rd highest salary

Ksharp — Thu, 08 Dec 2022 11:38:17 GMT

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.

Re: Find 3rd highest salary

animesh123 — Thu, 08 Dec 2022 11:53:17 GMT

Thanks @Ksharp I got it