Please help me understand, why does this logic fail?

[rluo0810]

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!!

[PGStats]

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

[acemanhattan]

@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]

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

[SASKiwi · Accepted solution]

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.