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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.