topic Re: Oracle Number(20,4) getting round off while loading in Teradata using SAS in SAS Programming

Oracle Number(20,4) getting round off while loading in Teradata using SAS

microeyes — Fri, 08 Mar 2019 08:42:50 GMT

Hi all,

I am loading data from Oracle to Teradata using SAS pass-through
Code example:
CONNECT TO ORACLE ()
INSERT INTO TDLIB.tblnm(fastload=yes)
Select * from connection to oracle(
Select * from oradb.ora_tblnm
)

In Oracle data is like : 99999999999999.99 (datatyep-Number(20,4)
But after loading in Teradata it gets round of to : 100000000000000.00

Tried with
DATA SET OPTION- DBTYPE
But though getting it like :99999999999999.9872 with decimal (20,4) And 99999999999999.98 with decimal (20,2)

I want it to be loaded as 99999999999999.9900 in Teradata

Need a solution for this.
Thanks.

Re: Oracle Number(20,4) getting round off while loading in Teradata using SAS

Peter_C — Fri, 08 Mar 2019 09:05:58 GMT

There is little hope that those 00 after .99 would be stored, unless you store it all as string

Re: Oracle Number(20,4) getting round off while loading in Teradata using SAS

Kurt_Bremser — Fri, 08 Mar 2019 09:09:26 GMT

As soon as you move data through SAS, you are subject to its limits, which means a maximum precision of 15 decimal digits because of the 8 byte real format that SAS uses for numbers.

I suggest you find a way to transfer the data more directly. Unload to a text file with proper formatting, and load from that, without using SAS.

Re: Oracle Number(20,4) getting round off while loading in Teradata using SAS

andreas_lds — Fri, 08 Mar 2019 09:09:57 GMT

Just guessing, but since sas numbers have at max 15 significant digits, some information is lost during transfer.

See http://support.sas.com/documentation/cdl/en/hostwin/63285/HTML/default/viewer.htm#numvar.htm for details about how numbers are stored.