turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- sas query how to find the n highest number

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-20-2015 12:32 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to pawandh

12-20-2015 06:06 AM

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:

- The outer query will select 23
- 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**

All Replies

Solution

12-21-2015
11:44 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to pawandh

12-20-2015 06:06 AM

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:

- The outer query will select 23
- 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**

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mohamed_zaki

12-21-2015 11:48 PM

Thanku for such a deep n perfect explanation.