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