05-14-2018 06:04 AM
We're optimizing a SAS DI Studio job so that it runs 100% on Netezza.
Netezza doesn't seem to tolerate NULL in DATE variables however. It also doesn't tolerate it if we set the date variable to '.'.
We've decided to enter a dummy date instead. It should be set to 01JAN1900 (in a SAS numeric DATE9 variable).
Do you know how I can set this date in a way that Netezza accepts? I've tried the TO_DATE() expression, but it doesn't work in a numeric variable. Can't convert it using INPUT() either.
05-14-2018 06:48 AM
TO_DATE() will work for explicit pass-through.
If you're using SAS SQL (implicit pass-through) then use for missing dates an expression like '01JAN1900'd and make sure that the variable you assign this value to has a format of date9. assigned - any date. format actually; just something which tells the SAS/Access engine that the SAS numeric variable contains a SAS Datetime value so it gets converted correctly to a Netezza DATE value.
If you have datetime values on the SAS side then use '01JAN1900 00:00:00'dt and a format of datetime21.