3rd highest value

Reply
Contributor
Posts: 64

3rd highest value

Hi All,

I am so puzzled that how does this code give the 3rd highest value from the dataset anand.aus. Please explain the code

"where 3= (select count(distinct (Salary)) from anand.aus b where a.salary<=b.salary)"

to me so that it could be eaiser to understand as I am a newbie.

proc sql;

select a.Salary

from anand.aus a

where 3= (select count(distinct (Salary)) from anand.aus b where a.salary<=b.salary);

quit;

Regards

Rahul

Super Contributor
Posts: 339

Re: 3rd highest value

Posted in reply to Rahul_SAS

It is an extremely ineficcient way to get the third highest value.

The logic is as follow:

For each value of Salary in the outer query select a.Salary, a subquery is executed on the same exact single variable of the same dataset with the following embeded logic:

     count(distinct salary)

     where a.salary<=b.salary

So for each value of a.salary, a full subquery is executed to count the number of salaries that are above or equal that value (b.salary => a.salary). If the count is equal to 3, hence if if the current a.salary has only exactly 3 values that are greater or equal to it, then this record is kept and output from the outer query as per the where 3= portion of the logic.

In particular, if the third highest salary in your dataset had replicates e.g. if your data were something like 50000 49000 48000 48000 48000 47000 46000 45000 44000

then the 3rd highest salary would actualy be output 3 times because all the records with salary=48000 would have the subquery output 3 as the value from the select count(distinct salary). ..

Occasional Contributor
Posts: 14

Re: 3rd highest value

Posted in reply to Rahul_SAS

data abc;
input h;
cards;
13
24
45
55
88
27
26
;
run;

proc sql;

select k.h,
1+(select count(distinct h) from abc
                 where k.h > abc.h)
         as dr
from abc as k
where calculated dr=3;

run;

Respected Advisor
Posts: 4,173

Re: 3rd highest value

Posted in reply to NagendraKumarK

Why not use PROC RANK and also have all the options to deal with ties.

Ask a Question
Discussion stats
  • 3 replies
  • 1352 views
  • 0 likes
  • 4 in conversation