topic Re: Generating error files in proc append in SAS Programming

Generating error files in proc append

helannivas88 — Sun, 21 Jul 2019 13:56:32 GMT

Hi,

Previously we have designed our SAS jobs in a such a way that data will be read from files( .xl or csv) and then will be appended into Netezza database using proc append statement.

If there are any errors , Error file (nzbad) will be generated during proc append statement . We have defined in proc append statement as

BULKLOAD=YES

BL_DATAFILE='/data/sas/abc.dat'
BL_USE_PIPE=NO
BL_DELETE_DATAFILE=Yes
BL_OPTIONS="logdir'/data/sas/o' maxErrors 1"

Now we have migrated to DB2 database but I cant able to use the same option mentioned above. For bulk load option , we have use BL_METHOD = CLILOAD , but when we use this we are not able to use BL_OPTIONS = "logdir ......maxErrors 1".

We have a requirement to generate a error file if there are any errors in the txt file as well we have to use CLILOAD option. Is there any way to achieve this ??? Thanks in Advance.

Re: Generating error files in proc append

Tom — Sun, 21 Jul 2019 15:31:40 GMT

I don't understand. Why would there be errors loading the data that you already processed with SAS.

Shouldn't you catch the errors in the SAS code and publish any errors from there.

Or just load the data with native database tools and use whatever logging those tools provide.

Re: Generating error files in proc append

Reeza — Sun, 21 Jul 2019 20:03:11 GMT

How are you connecting to your DB2 database, via Access to DB2 or Access to ODBC?

And which SAS version are you using, Viya appears more restrictive.

In general, it seems like using the CLILOAD does disable a bunch of other options, most of it is listed here:

https://documentation.sas.com/?docsetId=acreldb&docsetTarget=p1w9ial7770y6tn1rtfiuwtfg088.htm&docsetVersion=9.4&locale=en

The following are the available options when using CLILOAD, unfortunately most of the options you've specified are not available but perhaps some of the others allow you to get what you need?

@helannivas88 wrote:

Hi,

Previously we have designed our SAS jobs in a such a way that data will be read from files( .xl or csv) and then will be appended into Netezza database using proc append statement.

If there are any errors , Error file (nzbad) will be generated during proc append statement . We have defined in proc append statement as

BULKLOAD=YES

BL_DATAFILE='/data/sas/abc.dat'
BL_USE_PIPE=NO
BL_DELETE_DATAFILE=Yes
BL_OPTIONS="logdir'/data/sas/o' maxErrors 1"

Now we have migrated to DB2 database but I cant able to use the same option mentioned above. For bulk load option , we have use BL_METHOD = CLILOAD , but when we use this we are not able to use BL_OPTIONS = "logdir ......maxErrors 1".

We have a requirement to generate a error file if there are any errors in the txt file as well we have to use CLILOAD option. Is there any way to achieve this ??? Thanks in Advance.

Re: Generating error files in proc append

Patrick — Sun, 21 Jul 2019 23:07:16 GMT

@helannivas88

Different database, different rules! Well, actually, it's quite similar for all databases but you still need to read the docu (link @Reeza posted) and use a valid combination of options specific for the database you're connecting to.