Help using Base SAS procedures

Please help me understand, why does this logic fail?

Accepted Solution Solved
Reply
N/A
Posts: 1
Accepted Solution

Please help me understand, why does this logic fail?

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


Accepted Solutions
Solution
‎06-04-2015 10:56 PM
Super User
Posts: 3,261

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

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


All Replies
Respected Advisor
Posts: 4,936

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

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
Contributor
Posts: 46

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

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

Respected Advisor
Posts: 4,936

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

Posted in reply to acemanhattan

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
Solution
‎06-04-2015 10:56 PM
Super User
Posts: 3,261

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

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 334 views
  • 4 likes
  • 4 in conversation