I have SAS code with connects to DB2 using Pass thru ODBC.
When I run in SAS base, it connects and extracts the data okay.
When I run this same code from a DOS batch file, it extracts 0 data.
I don't see any errors in the log.
I am running SAS 9.3 on a Windows 7 64 bit.
The ODBC driver to DB2 is a 64 bit drive.
Any suggestions?
Terry
So the batch log simply says it created the SAS dataset with 0 observations?
Hi, this is the part of the log when run from Base SAS.
579
580 /* Local Passthrough query via ODBC to Database */
581 proc sql;
582 connect to ODBC(dsn="PRPDX" uid = USERID password = XXXXXXXXXX );
584 create table getPFX(compress = yes) as
585 select * from connection to ODBC(&SQL1);
NOTE: Compressing data set WORK.GETPFX decreased size by 76.21 percent.
Compressed is 29848 pages; un-compressed would require 125466 pages.
NOTE: Table WORK.GETPFX created, with 2634768 rows and 20 columns.
586 disconnect from ODBC;
587 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 2:24.17
cpu time 31.83 seconds
And this is the log when run from a DOS batch files.
285 /* Local Passthrough query via ODBC to Performix Database */
286 proc sql;
287 connect to ODBC(dsn="PRPDX" uid = USERID password = XXXXXXXXXX );
289 create table getPFX(compress = yes) as
290 select * from connection to ODBC(&SQL1);
NOTE: Table WORK.GETPFX created, with 0 rows and 20 columns.
291 disconnect from ODBC;
292 quit;
NOTE: SAS set option OBS=0 and will continue to check statements.
This might cause NOTE: No observations in data set.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.39 seconds
cpu time 0.12 seconds
No rows pulled and no errors.
Hm. That:
NOTE: SAS set option OBS=0 and will continue to check statements.
usually appears immediately after an ERROR message. So there must be something in that SQL that causes an ERROR, but doesn't display a proper message.
Two things that jump out at me from those log snippets:
- What is &SQL1? You have not shown all the code, nor the log with options mlogic mprint symblogen on.
- The log looks peculier to say the least, proc sql in interactive starts at line 581, but in batch 286. Why the difference in 300 lines of log?
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.