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.
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.