BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pawandh
Fluorite | Level 6

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
mohamed_zaki
Barite | Level 11

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

2 REPLIES 2
mohamed_zaki
Barite | Level 11

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
Fluorite | Level 6

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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