SAS Formats not migrating to Netezza

Reply
Contributor
Posts: 41

SAS Formats not migrating to Netezza

Hi All,

I trying to bulk load data to Netezza using the SAS/ACCESS to Netezza interface.  I am successful in loading the data. However, I keep getting the following message in the SAS log.  When I review the data bulk loaded Netezza the date information is not formatted correctly... Is there an easy way to get around this problem.  I have looked at the INDNZ_PUBLISH_FORMATS without success.

Any assistance in this matter would be grately appreciated.

Many thanks

G

Contributor
Posts: 41

Re: SAS Formats not migrating to Netezza

Posted in reply to gra_in_aus

Apologies forgot to post the message in the SAS log:

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

Many thanks

G

Super Contributor
Posts: 644

Re: SAS Formats not migrating to Netezza

Posted in reply to gra_in_aus

The note is a reminder that SAS does not attempt to replicate proprietary features in foreign DBMS tables.  SAS does use date formats to identify numeric columns in datasets that contain date data; however the formatting of the date to a particular requirement is left to the DBMS.

You do not describe what formatting problem you encountered.  If the date is formatted as a recognisable date in a different format then you need to provide the DBMS with the date format you require.  If the date appears as a date that is wrong (01JAN1960 is the usual culprit) then you may need to convert your dates to datetime values (datetime = DHMS(date, 0, 0, 0)) before DBMS load.  If the date does not appear as a date at all you may need to open a ticket with SAS support.

Richard

Contributor
Posts: 41

Re: SAS Formats not migrating to Netezza

Posted in reply to RichardinOz

Thanks Richard,

I am trying to create month keys (e.g. 201408) and dates formatted such as June 2014 from 01JUN2014.

Many thanks

G

Super Contributor
Posts: 644

Re: SAS Formats not migrating to Netezza

Posted in reply to gra_in_aus

In your response, is 201408 intended to be a number, or a date numeral formatted as YYMM6. ?

Similarly, in your data is 01JUN2014 a character string, or a date numeral formated as Date9. ?

Did you try converting SAS dates to datetime values?  You need ot apply a datetime format in SAS.

Richard

Trusted Advisor
Posts: 3,214

Re: SAS Formats not migrating to Netezza

Posted in reply to gra_in_aus

review your SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition (functions). All external dbms  systems have some different properties.

Functions have to be translated (implicit pass through).

Datatypes are being converted. SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition dependent on the associate SAS format.

That translation type must be a known one if you do not want get into some troubles.  Date Time Datetime are known but month not.    

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 5 replies
  • 359 views
  • 0 likes
  • 3 in conversation