queries have different results

[Ronein]

Hello

The following 2 queries have different results (number of rows).

Why?

proc sql;
create table A1 as
select distinct a.Identity,b.routing
from (select distinct Identity  from t1 ) as a
left join (Select Identity,routing  from t2) as b
on a.Identity=b.Identity
WHERE b.routing ne 'BUREAU'
;
quit;


proc sql;
create table A2(Where=(routing ne 'BUREAU')) as
select distinct a.Identity,b.routing
from (select distinct Identity  from t1) as a
left join (Select Identity,routing  from t2 ) as b
on a.Identity=b.Identity
;
quit;

[Patrick]

Can you create sample data that allow to replicate what you tell us? I didn't manage to do it in below code - but may-be you see something in your real data that allows to create data for a case in tables t1 or t2 that I missed.

data t1;
  input Identity;
  datalines;
1
1
2
2
1
1
;

data t2;
  input Identity routing $;
  datalines;
1 AA
1 XX
2 XX
2 XX
1 BUREAU
1 BUREAU
1 BUREAU
1 AA
1 XX
;

proc sql _method _tree;
  create table A1 as
    select distinct a.Identity,b.routing
      from (select distinct Identity  from t1 ) as a
        left join (Select Identity,routing  from t2) as b
          on a.Identity=b.Identity
        WHERE B.ROUTING NE 'BUREAU'
  ;
quit;

proc sql _method _tree;
  create table A2(WHERE=(ROUTING NE 'BUREAU')) as
    select distinct a.Identity,b.routing
      from (select distinct Identity  from t1) as a
        left join (Select Identity,routing  from t2 ) as b
          on a.Identity=b.Identity
  ;
quit;

[Tom]

Are your source datasets coming from an external database (such as Oracle, MySQL etc.)?

Those systems implement TRI level logic whereas SAS only implements BINARY logic.

In SAS the test (routing ne 'BUREAU') can only be TRUE or FALSE.  But if that test was pushed into an external database it could return neither TRUE nor FALSE if the value of ROUTING is null (what they call missing in most database systems).  In which case you might need to test (routing ne 'BUREAU' or routing is null).