Hello SAS Experts,
I am trying to get all records from dataset have which are not in dataset have1:
data have;
a='A';output;
a='B';output;
a='C';output;
run;
data have1;
b='E';output;
run;
proc sql;
create table want as
select * from have
where a not in (select a from have1);
quit;
I am wondering why there is no error message with "ERROR: Column a could not be found in the table/view identified with the correlation name have1."
If i change my code like this,i get the error message:
proc sql;
create table want as
select * from have x
where a not in (select y.a from have1 y);
quit;
Thank you for helping,
Without the alias SAS searches for a variable a in ANY table.
Not the behaviour you'd expect unfortunately.
Yes, that's right. Precisely for this i'm wondering why no error message.
Hi,
I don't have the answer but it seems that "a" has a special meaning in a subquery context. Indeed, if you replace
a with, for instance, ab in your request, the error shows up as expected.
proc sql;
create table want as
select * from have
where a in (select ab from have1)
;
quit;
ERROR: The following columns were not found in the contributing tables: ab.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
Other tests ;
Direct query
select a from have1 => Error message
Use of an existing table
create table want as
select * from have
where a in (select a from sashelp.cars)
=> No error message
Without the alias SAS searches for a variable a in ANY table.
Not the behaviour you'd expect unfortunately.
Thank you very much for your answers.
Regards,
Do you have any idea of why it is so ?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.