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

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

3 REPLIES 3
ballardw
Super User

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
Tourmaline | Level 20

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

Data never sleeps
ShiroAmada
Lapis Lazuli | Level 10

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.  

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!

What is Bayesian Analysis?

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.

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
  • 3 replies
  • 789 views
  • 0 likes
  • 4 in conversation