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.
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.
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.
You can use the _method and _tree PROC SQL options to better understand how SAS performs the query.
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.