topic Re: Netezza Bulk Load and Null dates using SAS/ACCESS interface in SAS Enterprise Guide

Netezza Bulk Load and Null dates using SAS/ACCESS interface

RonTrimnell — Wed, 20 May 2015 15:43:23 GMT

We're trying to bulk load Netezza data using the SAS/ACCESS interface. We've found one problem we can't resolve. If the date in a data source is 12/31/9999, the value gets treated as null and loads into Netezza as a Null. We want the actual date. We added the BL_DATAFILE option to keep the datafile. SAS is stripping out the date field. Does anyone know if there is some way to get the date to pass through without getting changed?

Re: Netezza Bulk Load and Null dates using SAS/ACCESS interface

TomKari — Wed, 20 May 2015 17:50:56 GMT

From a quick look at Netezza doc'n, it appears that 12/31/9999 is the maximum date value allowed in Netezza.

It looks like you're trying to do something legal. If it were me, I'd try a quick test of copying a small table from SAS to Netezza with date values 12/30/9999 and 12/31/9999, not using bulk load. They should both be loaded successfully. If they aren't, open a case with Tech Support.

If they both load successfully, maybe try loading the same small table, using bulk load?

These inter-product transfers can be tricky, but once they're resolved they usually work well.

Tom

Re: Netezza Bulk Load and Null dates using SAS/ACCESS interface

SASKiwi — Wed, 20 May 2015 20:00:32 GMT

What is the data source and what is the column type of the date in the source?

It sounds like a data translation problem as SAS can store 31 Dec 9999 OK:

data _null_;

date = '31dec9999'd;

format date date9.;

put date = ;

run;

Re: Netezza Bulk Load and Null dates using SAS/ACCESS interface

gra_in_aus — Wed, 01 Jul 2015 01:30:16 GMT

Try formatting the date to be datetime25.6 (from memory). I have tried in the past creating tables in Netezza using SAS kept getting the same sort of issues. When I used the datetime format it resolved.

Re: Netezza Bulk Load and Null dates using SAS/ACCESS interface

LinusH — Wed, 01 Jul 2015 07:15:22 GMT

You are sure that yo are trying to fill a DATE column in Netezza?

What format do have on that column in SAS?