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.
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.
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.
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.
Thank you! This worked for me.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: