proc sql question

[aurel_hogea]

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,

[LinusH]

In your sample the only column in have1 is b, not a.

[aurel_hogea]

Yes, that's right. Precisely for this i'm wondering why no error message. 

[gamotte]

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

[Reeza · Accepted solution]

Without the alias SAS searches for a variable a in ANY table. 

Not the behaviour you'd expect unfortunately. 

[aurel_hogea]

Thank you very much for your answers.

Regards,

[gamotte]

Do you have any idea of why it is so ?