DATA Step, Macro, Functions and more

proc sql question

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

proc sql question

[ Edited ]

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,


Accepted Solutions
Solution
‎08-29-2016 07:14 AM
Super User
Posts: 17,774

Re: proc sql question

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

Not the behaviour you'd expect unfortunately. 

View solution in original post


All Replies
Super User
Posts: 5,255

Re: proc sql question

In your sample the only column in have1 is b, not a.
Data never sleeps
Occasional Contributor
Posts: 5

Re: proc sql question

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

Regular Contributor
Posts: 194

Re: proc sql question

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

 

Solution
‎08-29-2016 07:14 AM
Super User
Posts: 17,774

Re: proc sql question

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

Not the behaviour you'd expect unfortunately. 

Occasional Contributor
Posts: 5

Re: proc sql question

Thank you very much for your answers.

 

Regards,

Regular Contributor
Posts: 194

Re: proc sql question

Do you have any idea of why it is so ?

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 304 views
  • 0 likes
  • 4 in conversation