BookmarkSubscribeRSS Feed
EinarRoed
Pyrite | Level 9

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.

1 REPLY 1
Patrick
Opal | Level 21

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 1006 views
  • 0 likes
  • 2 in conversation