BookmarkSubscribeRSS Feed
Gcruzg
Fluorite | Level 6

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

 

Thanks in advanced!

9 REPLIES 9
Kurt_Bremser
Super User

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.

Kurt_Bremser
Super User

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.

Ksharp
Super User
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;


LinusH
Tourmaline | Level 20

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.

Data never sleeps
Gcruzg
Fluorite | Level 6

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.

Kurt_Bremser
Super User

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.

LinusH
Tourmaline | Level 20

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

Data never sleeps
Ksharp
Super User
Could you try DBTYPE options ?

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


Gcruzg
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 7377 views
  • 2 likes
  • 4 in conversation