BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Malarkey
Obsidian | Level 7

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
Reeza
Super User

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

SASKiwi
PROC Star

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
Obsidian | Level 7

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
Obsidian | Level 7

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

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 .

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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