topic Re: Error while pulling the data from Teradata in SAS Enterprise Guide

Error while pulling the data from Teradata

srisai — Tue, 30 Nov 2010 21:33:52 GMT

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?

Re: Error while pulling the data from Teradata

srisai — Wed, 01 Dec 2010 15:45:39 GMT

Hi,

I got resolved this issue.

Thanks,
Kavi

Re: Error while pulling the data from Teradata

RedPlanet — Thu, 15 Jan 2015 23:09:31 GMT

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.

Re: Error while pulling the data from Teradata

mma — Mon, 12 Jun 2017 15:53:16 GMT

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

Re: Error while pulling the data from Teradata

LinusH — Tue, 13 Jun 2017 14:54:53 GMT

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

Re: Error while pulling the data from Teradata

PrakashAdhav1 — Mon, 15 Aug 2022 04:50:46 GMT

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

Re: Error while pulling the data from Teradata

PrakashAdhav1 — Mon, 15 Aug 2022 04:55:20 GMT

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

Re: Error while pulling the data from Teradata

Kurt_Bremser — Mon, 15 Aug 2022 05:49:41 GMT

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