Help using Base SAS procedures

want to know the flow of below subquery

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

want to know the flow of below subquery

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.

 


Accepted Solutions
Solution
‎09-19-2017 08:31 PM
Super User
Posts: 13,583

Re: want to know the flow of below subquery

Posted in reply to himanshu1

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.

View solution in original post


All Replies
Solution
‎09-19-2017 08:31 PM
Super User
Posts: 13,583

Re: want to know the flow of below subquery

Posted in reply to himanshu1

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.

Super User
Posts: 5,884

Re: want to know the flow of below subquery

Posted in reply to himanshu1

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

Data never sleeps
Frequent Contributor
Posts: 116

Re: want to know the flow of below subquery

[ Edited ]
Posted in reply to himanshu1

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.  

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 229 views
  • 0 likes
  • 4 in conversation