topic Re: Loading Data into DB2 Table in SAS Programming

Loading Data into DB2 Table

deleted_user — Mon, 23 Feb 2009 12:02:18 GMT

Dear SAS,

I am getting a problem related to the value rounding off while loading the data to DB2 table from SAS Dataset using PROC APPEND.

Details:

Issue:

A 16 digit numeric field which contains value for e.g. as 8853159845301049 in SAS data set gets rounded off to 8853159845301050 while populating in DB2 using proc append in the table loader.
NOTE:
The metadata for a field in SAS Dataset is -
Type - Numeric
Length - 8
Format - 16.
Informat - 16.

The metadata for a field in DB2 is -
Type - DECIMAL
Precision - 16
Scale - 0

Please suggest the way by which this issue can be prevented.

Regards
Deepu

Re: Loading Data into DB2 Table

Patrick — Thu, 26 Feb 2009 13:15:22 GMT

Hi Deepu

That's a nasty one!

I remember that there is a very good SAS whitepaper or SUGI/SEUGI paper explaining why this can happen - but I just can't find it anymore!!!

What I remember from this paper is that the cause of the problem is how numbers are digitally represented/stored in SAS vs. Oracle (or DB2) - and also under different platforms like Windows/Unix/zOS. It was very clear and obvious.

I believe the solution was using the round() function when loading to Oracle - but I'm just no more able to recall the details.

Let me know in case you find this paper. It's one I should store in "my box".

Cheers, Patrick

Just found this SAS note which points in the same direction. "endian byte order" seems to be a key word. Message was edited by: Patrick

Re: Loading Data into DB2 Table

deleted_user — Thu, 26 Feb 2009 15:13:56 GMT

Thanks Patrick For your reply,
But round() is not working in DB2,If you Find some thing let me know.
Thanks

Re: Loading Data into DB2 Table

Patrick — Thu, 26 Feb 2009 21:44:26 GMT

Found something:
http://support.sas.com/techsup/technote/ts654.pdf
HTH
Patrick