topic Re: proc sql question in SAS Programming

proc sql question

aurel_hogea — Mon, 29 Aug 2016 09:36:20 GMT

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,

Re: proc sql question

LinusH — Mon, 29 Aug 2016 09:54:53 GMT

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

Re: proc sql question

aurel_hogea — Mon, 29 Aug 2016 10:02:29 GMT

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

Re: proc sql question

gamotte — Mon, 29 Aug 2016 10:22:44 GMT

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

Re: proc sql question

Reeza — Mon, 29 Aug 2016 10:26:12 GMT

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

Not the behaviour you'd expect unfortunately.

Re: proc sql question

aurel_hogea — Mon, 29 Aug 2016 11:18:21 GMT

Thank you very much for your answers.

Regards,

Re: proc sql question

gamotte — Mon, 29 Aug 2016 13:13:28 GMT

Do you have any idea of why it is so ?