topic Re: SAS code runs fine in Base but not in batch in SAS Programming

SAS code runs fine in Base but not in batch

TGuy — Wed, 02 Nov 2016 12:47:55 GMT

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

Re: SAS code runs fine in Base but not in batch

Kurt_Bremser — Wed, 02 Nov 2016 13:32:13 GMT

So the batch log simply says it created the SAS dataset with 0 observations?

Re: SAS code runs fine in Base but not in batch

TGuy — Wed, 02 Nov 2016 13:38:06 GMT

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.

Re: SAS code runs fine in Base but not in batch

Kurt_Bremser — Wed, 02 Nov 2016 14:13:49 GMT

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.

Re: SAS code runs fine in Base but not in batch

RW9 — Wed, 02 Nov 2016 14:26:56 GMT

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?