SAS bug in select subquery?

[jffeudo86]

Is there a know bug and what's the fix?

proc sql;

create tableC as

select *

from tableA

where accountid in (select accountid from tableB);

quit;

if accountid is not a column in tableB it seems SAS takes the value of accountid from tableA.  Anyone has encountered this, and what's the fix?

[FreelanceReinh]

Hello @jffeudo86,

Last year there was a discussion about this in https://communities.sas.com/t5/SAS-Programming/Proc-sql-giving-result-when-expected-not-to/m-p/72350.... And that was not the first time someone stumbled across it (see the most recent post in that thread).

[Tom · Accepted solution]

---

@jffeudo86 wrote:

Is there a know bug and what's the fix?

proc sql;

create tableC as

select *

from tableA

where accountid in (select accountid from tableB);

quit;

if accountid is not a column in tableB it seems SAS takes the value of accountid from tableA.  Anyone has encountered this, and what's the fix?

---

Programmer error.  Be specific about what variables you want to select.

where tablea.accountid in (select tableb.accountid from tableB)

To see why it is not a bug consider that there are situations where you do want to use a variable from a different dataset in the sub-query.

[jffeudo86]

Thank you!  That makes sense although using a variable from outside the subquery is basically returning the number of rows of the subquery.

[novinosrin]

An addendum to elegant responses,  to discern is actually the situation analogous to the function of OR(logic gates) and the corresponding TRUTH tables. Alas, should that be too much to dig in, a better way to understand this -

if x=1 or 2; /*this is what's happening and will always be true*/

such that if and only if X is captured by the SQL optimizer access path