topic Re: SAS bug in select subquery? in SAS Programming

SAS bug in select subquery?

jffeudo86 — Fri, 27 May 2022 14:03:39 GMT

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?

Re: SAS bug in select subquery?

FreelanceReinh — Fri, 27 May 2022 14:27:36 GMT

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/723508. And that was not the first time someone stumbled across it (see the most recent post in that thread).

Re: SAS bug in select subquery?

Tom — Fri, 27 May 2022 14:54:17 GMT

@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.

Re: SAS bug in select subquery?

novinosrin — Fri, 27 May 2022 15:04:44 GMT

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

Re: SAS bug in select subquery?

jffeudo86 — Fri, 27 May 2022 15:59:14 GMT

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