Code should result in error but it does not

[MParthasarathy]

Hello SAS Users - Here is an example that is baffling me and my team mate. 

data test;
input name1 $ name2 $;
datalines;
AA BB
CC DD
EE FF
HH PP
;
run;

data name;
input code $;
datalines;
AA
FF
HH
;
run;

proc sql;
create table result as
select *  from test
where name1 in (select name1 from name) ;  /*name1 doesn't exist in the "name" dataset */
quit ;

NOTE: Table WORK.RESULT created, with 4 rows and 2 columns.

The last proc sql step should result in an error because there is no field called "name1" in dataset name but it does not give us an error.  Instead it does the search, and gives us the entire "test" dataset.  

But if I change the last proc sql to read like below: 

proc sql;
create table result as
select * from test
where name1 in (select x from name);
quit;

ERROR: The following columns were not found in the contributing tables: x.

Shouldn't I get an error response in both cases? Or maybe there is something I don't understand about SAS in this.  Thank you for any insight you can provide. 

[FreelanceReinh]

Hello @MParthasarathy,

This was discussed in the 2021 thread Proc sql giving result when expected not to. Please see the solution there. Does it answer your question?

[sbxkoenk · Accepted solution]

• Case when used with a subquery strange result..
  https://communities.sas.com/t5/SAS-Procedures/Case-when-used-with-a-subquery-strange-result/td-p/210...

• SQL subquery, wrong variable name, no error
  https://communities.sas.com/t5/SAS-Procedures/SQL-subquery-wrong-variable-name-no-error/td-p/606354

Koen

[MParthasarathy]

Thank you all for your help. I will be trying these ideas to ensure I understand what is happening and try to learn new habits so I don't inadvertently end up with unexpected results and think they are ok - all because I didn't get an error. 

[Kurt_Bremser]

Hint 

[Tom]

But there is a variable NAME1, so SAS assumed that is the variable you wanted.  

324  proc sql feedback;
325  create table result as
326  select *  from test
327  where name1 in (select name1 from name) ;
NOTE: Statement transforms to:

        select TEST.name1, TEST.name2
          from WORK.TEST
         where TEST.name1 in
               ( select TEST.name1
                   from WORK.NAME
               );

NOTE: Table WORK.RESULT created, with 4 rows and 2 columns.

328  quit ;

If you meant some other variable named NAME1 then say so.

329  proc sql feedback;
330  create table result as
331  select *  from test
332  where test.name1 in (select name.name1 from name) ;
ERROR: Column name1 could not be found in the table/view identified with the correlation
       name NAME.
ERROR: Unresolved reference to table/correlation name name.
NOTE: Statement transforms to:

        select TEST.name1, TEST.name2
          from WORK.TEST
         where TEST.name1 in
               ( select name.name1
                   from WORK.NAME
               );

333  quit ;