topic Re: Unable to load the table to teradata- how to fix this issue? in SAS Programming

Unable to load the table to teradata- how to fix this issue?

ilearnsas — Thu, 21 Nov 2019 02:10:17 GMT

METHOD 1-

libname hemi teradata user="&user." pw="&passwd." tdpid=***schema="*****" mode=teradata dbsliceparm=all;

/* upload to generate a teradatatable*/

proc sql;

create table db.table

as select * from mytable;

quit;

It gives me this error.

ERROR: Teradata insert: Numeric overflow occurred during computation. SQL statement was: USING ("Col1"
DATE,"Col2" FLOAT,"Col3" FLOAT,"Col4" FLOAT,"Col5"
DECIMAL(4,2),"Col6" DECIMAL(4,2))INSERT INTO db."table"
("Col1","Col2","Col3","Col4","Col5","Col6") VALUES
(:"Col1",:"Col2",:"Col3",:"Col4",:"Col5",:"Col6"). Insert
substitution values are not shown.

WARNING: File deletion failed for db.table.DATA.

Instead I tried this method 2 libname below and it worked.

METHOD 2-

libname hemi teradata user="&user." password="&passwd." tdpid="****" database=**mode=teradata bulkload=Yes fastload=yes;

The table is created but missing a few rows i.e. 407 instead of 412 probably because of fastload and bulkload options. How do I fix this?

Thanks for your help.

Re: Unable to load the table to teradata- how to fix this issue?

ChrisNZ — Thu, 21 Nov 2019 03:25:06 GMT

1. You need to look at the values that cause the issue.

2. Using proc append plus fastload is often the preferred option to this type of operation.

Re: Unable to load the table to teradata- how to fix this issue?

s_lassen — Thu, 21 Nov 2019 08:40:33 GMT

The message you get is probably because SAS creates the columns in Teradata by looking at the formats you have assigned to the variables. So the problem is probably with COL5 og COL6 (which SAS tries to map to DECIMAL(4,2) variables). Some of the values for one or both of these variables may be too large to fit in the format. Try changing or removing the formats before loading to Teradata, or use the DBTYPE dataset option to force a different column type, e.g.

proc sql;
create table db.table(dbtype=(COL5='numeric(12,2)'))
as select * from mytable;  
quit;

When bulkloading, only the rows that contain the offending values are not inserted, the whole INSERT operation is not rolled back, which is why you get too few rows in the output table. With the SQL solution, the whole statement is rolled back after encountering an error.

Re: Unable to load the table to teradata- how to fix this issue?

ilearnsas — Thu, 21 Nov 2019 14:54:13 GMT

Thank you! This worked for me.