Desktop productivity for business analysts and programmers

Netezza Bulk Load and Null dates using SAS/ACCESS interface

Reply
Frequent Learner
Posts: 1

Netezza Bulk Load and Null dates using SAS/ACCESS interface


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?

PROC Star
Posts: 1,146

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

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

Super User
Posts: 3,235

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

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;

Contributor
Posts: 41

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

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.

Super User
Posts: 5,386

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

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

What format do have on that column in SAS?

Data never sleeps
Ask a Question
Discussion stats
  • 4 replies
  • 541 views
  • 0 likes
  • 5 in conversation