BookmarkSubscribeRSS Feed
TGuy
Calcite | Level 5

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

4 REPLIES 4
TGuy
Calcite | Level 5

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.

Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

 

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
  • 4 replies
  • 994 views
  • 1 like
  • 3 in conversation