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

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

Malarkey — Thu, 21 Jul 2016 15:30:00 GMT

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?

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

Reeza — Thu, 21 Jul 2016 17:33:03 GMT

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

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

Malarkey — Thu, 21 Jul 2016 17:35:16 GMT

I will do that, Reeza. Thanks!

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

SASKiwi — Thu, 21 Jul 2016 19:49:14 GMT

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.

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

Ksharp — Fri, 22 Jul 2016 01:46:27 GMT


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 .

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

Malarkey — Wed, 27 Jul 2016 14:22:37 GMT

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;

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

Malarkey — Wed, 27 Jul 2016 14:25:00 GMT

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;