topic Re: Code should result in error but it does not in SAS Programming

Code should result in error but it does not

MParthasarathy — Fri, 14 Jun 2024 18:53:11 GMT

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.

Re: Code should result in error but it does not

FreelanceReinh — Fri, 14 Jun 2024 21:28:01 GMT

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?

Re: Code should result in error but it does not

sbxkoenk — Fri, 14 Jun 2024 21:32:33 GMT

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/210931
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

Re: Code should result in error but it does not

Kurt_Bremser — Sat, 15 Jun 2024 07:04:04 GMT

Hint

Re: Code should result in error but it does not

Tom — Sat, 15 Jun 2024 16:05:23 GMT

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 ;

Re: Code should result in error but it does not

MParthasarathy — Tue, 18 Jun 2024 13:16:22 GMT

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.