BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
jffeudo86
Quartz | Level 8

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

 

View solution in original post

4 REPLIES 4
FreelanceReinh
Jade | Level 19

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
Super User Tom
Super User

@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
Quartz | Level 8

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

novinosrin
Tourmaline | Level 20

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 841 views
  • 3 likes
  • 4 in conversation