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?
You should talk to SAS Support here, they'll help you debug this quickly and if it is a bug they should be notified.
You should talk to SAS Support here, they'll help you debug this quickly and if it is a bug they should be notified.
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.
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;
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;
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 .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.