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

Hi everybody,

I'm pretty new to SAS, studying Proc Sql stuff right now. I have came up with the code below,

proc sql;

  select customer_firstname, customer_lastname

  from orion.customer_dim

  where 'F' in (select gender

  from orion.customer);

quit;

Why would this return every result within the customer_dim table regardless of 'M' or 'F'? I understand that an equal sign can't be used, since the subquery returns multiple results. I also understand that I could write a 'WHERE gender = 'F'' within the subquery, but why will this not work? I'm really looking for an explanation as to why the logic fails here, is it because a 'F' character constant cannot be used with the operator 'In'? I know that you can use character constant with comparison operator like the "=" sign.

Thanks!!

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

Your WHERE statement asks the question: is the gender F in table CUSTOMER - this is always true. Since it is always true your SELECT reads all rows from CUSTOMER_DIM.

View solution in original post

4 REPLIES 4
PGStats
Opal | Level 21

What your query is missing is a correlation between the main query and the subquery. Assuming customer_firstname and customer_lastname columns exist in both tables, try

proc sql;

  select customer_firstname, customer_lastname

  from orion.customer_dim as a

  where 'F' in (select gender

  from orion.customer

  where customer_firstname = a.customer_firstname and customer_lastname=a.customer_lastname);

quit;

PG

PG
acemanhattan
Quartz | Level 8

@PGStats,

I'm familiar with using the "as" operator in the following two ways:

proc sql;

create table created_table as

select k.complicated_variable as variable

from other_table k

quit;

so I see it used here to tell SAS what the table is going to contain, and then to tell SAS what to rename a variable. What does your use of the as operator tell SAS to do?

PGStats
Opal | Level 21

It simply is that

from other_table k


can be written


from other_table as k


"as" is optional. I find that the extra word adds legibility.

PG

PG
SASKiwi
PROC Star

Your WHERE statement asks the question: is the gender F in table CUSTOMER - this is always true. Since it is always true your SELECT reads all rows from CUSTOMER_DIM.

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
  • 4 replies
  • 856 views
  • 4 likes
  • 4 in conversation