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