want to know the flow of below subquery

[himanshu1]

below query finding the third highest mark holder

data abc1;
input id name$ sal;
datalines;
10 a 5000
11 b 10000
12 c 10000
13 a 9000
14 b 11000
15 c 4000
16 a 5800
17 b 11000
18 c 12000
;
run;

proc sql;
select * from abc1 as e1
where 2=(select count(distinct sal) from abc1 as e2 where e2.sal<e1.sal and e2.name=e1.name);
quit;

i want to know the flow of this query. how second dataset is created and how the inner where clause is exected before executing the outer loop.

[ballardw · Accepted solution]

Without a "create table" there is no second data set. The "select * from abc1 as e1" creates an alias to reference one instance of the data set abc1 as does the "select count(distinct sal) from abc1 as e2 " which creates an alias of the count of distinct values from abc1.

Parantheses control order of operations in queries similar to within caluculations; the innermost set of () is resolved to send to the part outside for use.

[LinusH]

You can use the _method and _tree PROC SQL options to better understand how SAS performs the query.

[ShiroAmada]

Small world - https://communities.sas.com/t5/Base-SAS-Programming/Self-join-in-SAS/m-p/394631

 Anyways, you can always break the query (and the subquery) into separate create tables.  That way, you will see went and what has been done.