DATA Step, Macro, Functions and more

sas query how to find the n highest number

Accepted Solution Solved
Reply
Contributor
Posts: 59
Accepted Solution

sas query how to find the n highest number

 

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.


Accepted Solutions
Solution
‎12-21-2015 11:44 PM
Super Contributor
Posts: 490

Re: sas query how to find the n highest number

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

 

View solution in original post


All Replies
Solution
‎12-21-2015 11:44 PM
Super Contributor
Posts: 490

Re: sas query how to find the n highest number

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

 

Contributor
Posts: 59

Re: sas query how to find the n highest number

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 213 views
  • 1 like
  • 2 in conversation