BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aurel_hogea
Calcite | Level 5

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,

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

Not the behaviour you'd expect unfortunately. 

View solution in original post

6 REPLIES 6
LinusH
Tourmaline | Level 20
In your sample the only column in have1 is b, not a.
Data never sleeps
aurel_hogea
Calcite | Level 5

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

gamotte
Rhodochrosite | Level 12

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
Super User

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

Not the behaviour you'd expect unfortunately. 

aurel_hogea
Calcite | Level 5

Thank you very much for your answers.

 

Regards,

gamotte
Rhodochrosite | Level 12

Do you have any idea of why it is so ?

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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