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

Reply
Frequent Contributor
Posts: 100

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

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.

Respected Advisor
Posts: 4,743

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

Posted in reply to EinarRoed

@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.

Ask a Question
Discussion stats
  • 1 reply
  • 94 views
  • 0 likes
  • 2 in conversation