DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

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

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?

 


Accepted Solutions
Solution
‎07-27-2016 10:22 AM
Super User
Posts: 17,792

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

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

View solution in original post


All Replies
Solution
‎07-27-2016 10:22 AM
Super User
Posts: 17,792

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

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

Contributor
Posts: 27

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

I will do that, Reeza. Thanks!
Super User
Posts: 3,102

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

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.

Contributor
Posts: 27

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

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;
Contributor
Posts: 27

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

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;
Super User
Posts: 9,676

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


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 .

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 407 views
  • 4 likes
  • 4 in conversation