08-29-2016 04:10 AM
Let me explain you what is my little problem. I have a SAS data set with 3 columns with Numerical format (Numeric Length 8), and I would like to insert them into a Teradata table with 3 columns with the next formats: Decimal(11,3), Integer, Decimal(11,3). Nonetheless I receive a ROLLBACK error.
NOTE: I have enough permissions in the teradata library and I have a good conectivity.
The SAS code I am using is the following:
insert into tdt.table_1
select C_1 format=BEST11.3,
Please, can anybody explain me how to change formats in order to move data from SAS into Teradata?
Note: I have been reading the SAS Support link for Teradata formats, but I couldn't achieve the solution.
Thanks in advanced!
08-29-2016 04:23 AM
A number is a number is a number in SAS, meaning it is always stored as a 64 bit real number.
The formats have no effect on storage.
I guess you are better off creating a new table from SAS in Teradata and handling the insert from that table in Teradata, using explicit pass-through.
08-29-2016 04:35 AM
PS my preferred method for transfers back to the production database is to create a text file (usually fixed-width columns) and let the DBA people make the import.
08-29-2016 06:20 AM
Those formats are belong to Teradata. So you need create a table with these format at Teradata side and load SAS dataset into Teradata ,then use INSERT by Pass-through OR just try: proc append base=x data=have;run;
08-29-2016 08:36 AM
The formats specified in an insert statement should have no affect to the actual data flow. So you need to make sure that your actual data values in SAS matches the expected data type in Teradata. You may need to to truncate/round to create integers as an example.
You can specify formats in SAS to control data types and length in Teradata, but only during table creation. And again, it does not affect the physical data format comming from SAS.
08-29-2016 08:42 AM
I know what you mean.
The point is that the table's skeleton is already created on Teradata, so I cannot change the formats in it. That's why I need to know how to convert the sas formats into others to move them to Teradata tables.
08-29-2016 09:10 AM
Look what SAS types are created if you import the table into sas:
data table_1; set tdt.table_1 (obs=1); run;
This might give you a clue how your SAS data should be structured.
08-29-2016 09:12 AM
As said, you need to use SAS function to truncate/round your values, so they match the Teradata data types/precision.
Take a look at ROUND, CEIL, FLOOR...
09-05-2016 04:08 AM
Finally, with some of your advices, finally I solved the problem. Here it goes the solution:
I defined the library with a libname statement using the following options:
liname tera teradata user=xxx passwrod=xxx server=xxxx schema=xxxx sql_options=all mode=teradata;
The, in the insert-into sql sentences i used the option (fastload=yes), as the following example:
insert into tera.table_1 (fastload=yes)
select * from lib.table2;
Once again, thank you so much for your advices and I hope that my solution will help you someday.