Setting a date value in a SAS job that runs on Netezza

[EinarRoed]

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.

[Patrick]

@EinarRoed

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.