sas query how to find the n highest number

[pawandh]

have to find the n highest no.

data salary;
input sal;
cards;
23
12
.
45
0
54
-21
43
;
run;

%let N=3;
proc sql;
select a.sal from salary a where &N= (select count(distinct sal) from salary b where a.sal<=b.sal);
quit;

please explain the logic that is used in this query.

[mohamed_zaki · Accepted solution]

This is kind of Subqueries called "Correlated Subqueries"

Where subquery requires a value or values to be passed to it by the outer query. Where the outer query select the row from the its table and then passes the value to the subquery. 

So in the first step for example:

1. The outer query will select 23
2. then pass it to the sub query

So it will look like: 

select count(distinct sal) from salary b where 23<=b.sal;

so it will return the couunt of all sal < = 23: count(23: 23,45,54,43) = 4,  which is the order of the number 23 in descending order

3. Then the outer quey will look like

proc sql;
select a.sal from salary a where 3= 4;
quit;

so it will not return any result as the condition in where is not valid.

....... it will continue like this till the subquery return 3 and this will happen when the outer query pass the third highest number which is 43 in your case.

when that happen the sub query will look like

select count(distinct sal) from salary b where 43<=b.sal;

so it will return the couunt of all sal < = 43: count(45,54,43) =3

Then the outer quey will look like

proc sql;
select a.sal from salary a where 3=3;
quit;

in that case the where statment will be valid and the outer query will return 43

[pawandh]

Thanku for such a deep n perfect explanation.