Error while pulling the data from Teradata

[srisai]

Hi All,

Here am facing an error while extracting the data from Teradata to SAS by using SQL pass through facility in SAS?EG.

ERROR: At least one of the columns in this DBMS table has a datatype that is not supported by this engine.

what could be the reason?
Please advice?

[srisai]

Hi,

I got resolved this issue.

Thanks,
Kavi

[RedPlanet]

SAS/Access to Teradata does not support BIGINT data, and it is usually the offending data type.  However, other data types are supported but still may cause data truncation and errors.  For example, DECIMAL(38,15) is supported by SAS/Access, but it has an invalid SAS format, so it creates a runtime format error in SAS.

An easy way to find BIGINT or other unsupported data is to do a Proc Contents on the table using the libname access method.  The erroneous columns will be in the SAS log.  You can also look in the listing for DECIMAL values where they are longer than 15 digits -- you may want to query the max and min for those columns before you cast them to something that SAS can read.

SAS only represent 15 digits with exact precision in 64-bit operating systems.  You need to cast those bigger decimal types as DECIMAL(15) if they are in bounds, or CHARACTER(20) (or larger) if they are not.  Note that character keys are discouraged because they are slow to access in joins and they take up more space.

[PrakashAdhav1]

Hi srisai,

Can we do this convertion using implicit libname and in Data Step.?

(I am facing the same issue while reading the data from Teradata and many columns getting dropped.)

[Kurt_Bremser]

---

@PrakashAdhav1 wrote:
Hi srisai,

Can we do this convertion using implicit libname and in Data Step.?

---

No. You either need to do it in explicit pass-through or have a view created in Teradata (which you can also do in explicit pass-through).

[mma]

Hello Kavi,

I'm facing the same issue and i wanted to ask you how did you solve it? I'm doing a query over a table that has one column not supported and even if i'm not selecting it, it's giving me this error.

Thanks in advance!

Regards,

Mónica

[PrakashAdhav1]

Hi,

Can we do this convertion using implicit libname and in Data Step. and read the data from Teradata ?

(I am facing the same issue while reading the data from Teradata and many columns getting dropped.)

[LinusH]

I think that this is explained in many threads.
You need to either
- use explicit SQL pass through
- have a view created in TD that either omits or casts columns w unsupported data types