BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
3 REPLIES 3
Patrick
Opal | Level 21
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
deleted_user
Not applicable
Thanks Patrick For your reply,
But round() is not working in DB2,If you Find some thing let me know.
Thanks

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
  • 3 replies
  • 853 views
  • 0 likes
  • 2 in conversation