3

Help understand why erroneous inner query does not make outer query erroneous

The following query returns 19 

proc sql;
select count(distinct name)
    from sashelp.class
        where name in (select name from sashelp.iris
                        where species is not missing)
;quit; *returns 19;

However, I would expect it to return an error, because the inner query does indeed return an error (because the column 'name' is not found in sashelp.iris):

proc sql;    
select name from sashelp.iris
                            where species is not missing
;quit; *returns an error (column not found);

Can some explain the logic why I am not getting an error message in the first instance?

Rasmus Larsen
  • 5,721
  • 8
  • 47
  • 79

1 Answers1

6

You did not qualify the reference to name so it used the only variable it found called name. So you ran this query:

proc sql;
select count(distinct A.name)
  from sashelp.class A
  where A.name in
(select A.name
  from sashelp.iris B
  where B.species is not missing
)
;
quit;

If you actually refer to NAME from IRIS you will get the error message.

220   proc sql;
221   select count(distinct A.name)
222     from sashelp.class A
223     where A.name in
224   (select B.name
225     from sashelp.iris B
226     where B.species is not missing
227   )
228   ;
ERROR: Column name could not be found in the table/view identified with the correlation name B.
ERROR: Unresolved reference to table/correlation name B.
229   quit;
Tom
  • 47,574
  • 2
  • 16
  • 29
  • Can you expand on your answer? I'm still confused by your first PROC SQL step, what does the inner query `select A.name from sashelp.iris B ...` return? – Quentin Jul 09 '19 at 20:22
  • 1
    Try it and see. It should return that current value of NAME repeat X times where X is the number of observations in IRIS with non missing species. Normally you would use something like A.NAME in the WHERE clause of the sub query. – Tom Jul 09 '19 at 20:38
  • Ahh, thanks. I wasn’t thinking of it as a *correlated* subquery, which executes once for each record in sashelp.class. – Quentin Jul 10 '19 at 08:57