topic Re: want to know the flow of below subquery in SAS Procedures

want to know the flow of below subquery

himanshu1 — Mon, 18 Sep 2017 14:13:32 GMT

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.

Re: want to know the flow of below subquery

ballardw — Mon, 18 Sep 2017 15:44:50 GMT

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.

Re: want to know the flow of below subquery

LinusH — Mon, 18 Sep 2017 17:44:46 GMT

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

Re: want to know the flow of below subquery

ShiroAmada — Tue, 19 Sep 2017 02:38:19 GMT

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.