topic Loading data from SAS to Teradata in SAS Programming

Loading data from SAS to Teradata

Gcruzg — Mon, 29 Aug 2016 08:10:34 GMT

Hello everyone!

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:

proc sql;

insert into tdt.table_1

select C_1 format=BEST11.3,

C_2 format=BEST11,

C_3 format=BEST11.3

from lib.table_2;

quit;

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.

http://support.sas.com/documentation/cdl/en/acreldb/69039/HTML/default/viewer.htm#n0v7nh4ylrihtin1te8xl0q3dvzv.htm

Thanks in advanced!

Re: Loading data from SAS to Teradata

Kurt_Bremser — Mon, 29 Aug 2016 08:23:36 GMT

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.

Re: Loading data from SAS to Teradata

Kurt_Bremser — Mon, 29 Aug 2016 08:35:22 GMT

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.

Re: Loading data from SAS to Teradata

Ksharp — Mon, 29 Aug 2016 10:20:29 GMT

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;

Re: Loading data from SAS to Teradata

LinusH — Mon, 29 Aug 2016 12:36:27 GMT

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.

Re: Loading data from SAS to Teradata

Gcruzg — Mon, 29 Aug 2016 12:42:46 GMT

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.

Re: Loading data from SAS to Teradata

Kurt_Bremser — Mon, 29 Aug 2016 13:10:07 GMT

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.

Re: Loading data from SAS to Teradata

LinusH — Mon, 29 Aug 2016 13:12:46 GMT

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...

Re: Loading data from SAS to Teradata

Ksharp — Tue, 30 Aug 2016 02:22:56 GMT

Could you try DBTYPE options ?

data teradata.temp(dbtype=(a='Decimal(11,3)' b='char(20)'));
 set sas.have;
run;

Re: Loading data from SAS to Teradata

Gcruzg — Mon, 05 Sep 2016 08:08:01 GMT

Hello everyone!

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:

proc sql;

insert into tera.table_1 (fastload=yes)

select * from lib.table2;

quit;

Once again, thank you so much for your advices and I hope that my solution will help you someday.