topic Re: SAS teradata upload error in SAS Programming

SAS teradata upload error

ZeroSeven — Tue, 09 Jul 2019 12:01:26 GMT

I'm trying to upload data to Teradata using:

proc sql;

create table v4 as

select

EXTRACTION_DATE format=DATE9.,

LIMIT as LIMIT_AMT format=best32.

from work.x

quit;

proc sql;

insert into tera.y

select * from v4;

quit;

I get the error:

ERROR: On an insert, update, or index operation, the value applied to a Teradata 8 byte decimal was out of range - greater than

9.223372036854775808E18.

How do I solve this?

Re: SAS teradata upload error

tsap — Tue, 09 Jul 2019 18:42:57 GMT

Can you provide some additional information? Some dummy data that shows us what some values would look like on the work.x table, what they look like on the v4 table, the full log from the creation of the v4 table to just after the error message, etc.

Thanks

Re: SAS teradata upload error

tsap — Tue, 09 Jul 2019 18:45:57 GMT

One last question, what are the formats for these two fields on the already existing tera.y table? Can you provide dummy examples for that data as well?

Re: SAS teradata upload error

LinusH — Tue, 09 Jul 2019 19:26:00 GMT

Or more specifically, what is data type and precision in the TD table?

Re: SAS teradata upload error

Tom — Tue, 09 Jul 2019 19:40:24 GMT

Show the definition of the table you are loading into. From the Teradata documentation an 8 byte decimal value is large enough for decimal numbers with a wide of 10 to 18 digits. The number in your error message has 19 digits. Note also that when you define the variable in Teradata you need to specify how many of those digits are to the right of the decimal point, so the upper bound could be even lower.

Also SAS cannot store 19 digits exactly as a number since it uses 8 byte floating point binary numbers. The maximum number of decimal digits is 15. So if I read in

9,223,372,036,854,775,808

and print it back out it becomes

9,223,372,036,854,777,856

Notice how the last four digits have changed.

What do those number represent? Are they actual numbers that you want to compare? Do you really care about the 16-19 th least significant decimal digits?

Perhaps you just need to create the field as a floating point number in Teradata instead of a decimal number.

Re: SAS teradata upload error

ZeroSeven — Wed, 10 Jul 2019 05:50:17 GMT

More information

The limit_amt variable in Teradata is defined as

LIMIT_AMT DECIMAL(32,9) DEFAULT 0.000000000 COMPRESS ,

The limit that I'm trying to upload is

10917118759

The format in SAS is

NAME	TYPE	LENGTH	VARNUM	LABEL	FORMAT	FORMATL	FORMATD	INFORMAT	INFORML
LIMIT_AMT	1	8	7		BEST	16	2	BEST	32

Lastly - the dataset on Teradata has been predefined and I don't have access to alter the definitions there

Re: SAS teradata upload error

ZeroSeven — Wed, 10 Jul 2019 08:33:15 GMT

After further investigation my question now is:

How do I upload a value gt 999,999,999.9 to Teradata where the field is decimal(32,9)?

I either get the

ERROR: On an insert, update, or index operation, the value applied to a Teradata 8 byte decimal was out of range - greater than

9.223372036854775808E18.

ERROR: Teradata insert: Numeric overflow occurred during computation.

Re: SAS teradata upload error

Tom — Wed, 10 Jul 2019 12:06:51 GMT

Teradata does not want more than 9 decimal places. You told SAS it could format the number with up to 30 decimal places.

Try telling SAS to format the number with no more than 9 decimal places.

Try chaning the format attached to the variable from BEST32. to 32.9 and see if it works.

You are still not going to be able to get SAS to store more than 15 decimal digits exactly

Re: SAS teradata upload error

ZeroSeven — Wed, 10 Jul 2019 12:45:49 GMT

Thanks, Tom

Changing formats on the SAS side does not seem to work

It seems that the number is too large, any value less than or equal to 999,999,999.9 gets inserted into the Teradata, any number larger and I get the error

Re: SAS teradata upload error

Tom — Wed, 10 Jul 2019 12:55:39 GMT

Upload to a volatile table where the variable is defined as float and then copy from there into the real target using a passthru query.

Re: SAS teradata upload error

MelissaN — Thu, 01 Aug 2019 18:17:14 GMT

Hi Tom,
I ran into a similar issue with an error saying "On an insert, update, or index operation, the value applied to a Teradata integer was out of range - greater than 2147483647."
I ran the query couple times before but I didn't get any error. Double check all of my integer value but none of them exceed 2147483647. Could you please advise what would cause an error? Thanks much.