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

Innovate_SAS_Blue.png

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. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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