topic Re: Please help me understand, why does this logic fail? in SAS Procedures

Please help me understand, why does this logic fail?

rluo0810 — Thu, 04 Jun 2015 22:41:54 GMT

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

Re: Please help me understand, why does this logic fail?

PGStats — Fri, 05 Jun 2015 02:54:03 GMT

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;

Re: Please help me understand, why does this logic fail?

SASKiwi — Fri, 05 Jun 2015 02:56:48 GMT

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.

Re: Please help me understand, why does this logic fail?

acemanhattan — Sat, 01 Aug 2015 14:53:03 GMT

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

Re: Please help me understand, why does this logic fail?

PGStats — Sat, 01 Aug 2015 17:07:17 GMT

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.