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?
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
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;
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.
You are sure that yo are trying to fill a DATE column in Netezza?
What format do have on that column in SAS?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.