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