Pass-Through SQL returns different results than equivalent data step or proc sql using libname?

[Malarkey]

I'm hoping someone here knows what's going on...

I just upgraded to SAS 9.4.  I have one data step and two proc SQL procs that I think should be returning the same result set from SQL Server:

libname diablo   odbc DATABASE=DEV_Diablo               
                      schema=tTrans;
proc sql;

create table test as

select t.*

FROM Diablo.Trans         t

where t.CntyCd      = '51059'
;

quit;
proc sql;

connect to odbc (dsn="DEV_Diablo");

create table test1 as
select * from connection to odbc
(
SELECT *
  FROM [Diablo].[tTrans].[Trans]
  where CntyCd = '51059'
)
;
disconnect from odbc;
quit;
data test2;
  set diablo.Trans;

where CntyCd = '51059';

run;

[Diablo].[tTrans].[Trans] is a honking big table, and CntyCd 51059 is my home county of Fairfax in Virginia - it's pretty populous.  I would expect at least one million rows to return (population = 1.1 million).  But here's what I get:

1    libname diablo   odbc DATABASE=DEV_Diablo
2                          schema=tTrans;
NOTE: Libref DIABLO was successfully assigned as follows:
      Engine:        ODBC
      Physical Name: DEV_Diablo
3    proc sql;
4
5    create table test as
6
7    select t.*
8
9    FROM Diablo.Trans         t
10
11   where t.CntyCd      = '51059'
12   ;
NOTE: Table WORK.TEST created, with 415 rows and 395 columns.

13
14   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.62 seconds
      cpu time            0.21 seconds

29   proc sql;
30
31   connect to odbc (dsn="DEV_Diablo");
32
33   create table test1 as
34   select * from connection to odbc
35   (
36   SELECT *
37     FROM [Diablo].[tTrans].[Trans]
38     where CntyCd = '51059'
39   )
40   ;
NOTE: Table WORK.TEST1 created, with 3420645 rows and 395 columns.

41   disconnect from odbc;
42   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           19:44.13
      cpu time            4:55.51

49   data test2;
50     set diablo.Trans;
51
52   where CntyCd = '51059';
53
54   run;

NOTE: There were 415 observations read from the data set DIABLO.Trans.
      WHERE CntyCd='51059';
NOTE: The data set WORK.TEST2 has 415 observations and 395 variables.
NOTE: DATA statement used (Total process time):
      real time           0.40 seconds
      cpu time            0.04 seconds

The results from the pass-through SQL are what I would expect from the other two methods, as well.  I'll be using pass-through on this project, but the fact that the other two methods don't return the same result set is making me nervous.  Is this a bug?

[Reeza · Accepted solution]

You should talk to SAS Support here, they'll help you debug this quickly and if it is a bug they should be notified.

[Malarkey]

I will do that, Reeza. Thanks!

[SASKiwi]

Try running your queries with these troubleshooting options:

options sastrace = ',,,d' SASTRACELOC = SASLOG;

It will write extra info to your SAS log that might help explain the differences in your queries.

[Malarkey]

As @Reeza suggested, I opened a ticket with SAS.  They suggested adding the qualifier parameter to my libname statement, and that did the trick:

libname diablo   odbc DATABASE=DEV_Diablo               
                      schema=tTrans qualifier=Diablo;

[Malarkey]

As @Reeza suggested, I opened a ticket with SAS.  They suggested adding the qualifier parameter to my libname statement and that did the trick:

libname diablo   odbc DATABASE=DEV_Diablo               
                      schema=tTrans qualifier=Diablo;

[Ksharp]

DataBase Product like Oracle DB2 ...  they all treat MISSING and NULL are different value , But SAS is going to treat them the same 
thing. 
Maybe that is the reason why you got different result .