Desktop productivity for business analysts and programmers

Reading BIGINT data from teradata

Reply
Regular Contributor
Posts: 155

Reading BIGINT data from teradata

Hi,

I am trying to read a column with BIGINT datatype from teradata . I am getting the following note

ERROR: At least one of the columns in this DBMS table has

a datatype that is not supported by this engine.

Is there any way to resolve this error?

Thanks in advance,

Regards,

Sheeba Swaminathan

Valued Guide
Posts: 3,206

Re: Reading BIGINT data from teradata

check and understand the datatypes as first step. See the notes about precision on floating numbers with SAS.

SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition  (Teradata)

By that it is not making sense transforming bigint to float as it can harm you by losing numbers.

You can transform the Bigint type in a character approach  as other databases numbers.

That is thinking number is:

- float with SAS and many others and numbers

- in databases many times it are reality constraints to a limited range of characters.

(some exceptions like packed...)

Some Hollerith age inheritance is causing some confusing by many of us.

I you have 9.3 (DS2 experimental) / 9.4 (DS2 operational) you could look at using DS2. SAS(R) 9.4 DS2 Language Reference, Second Edition

As that one is supporting those other datatypes being used at DBMS systems

---->-- ja karman --<-----
Regular Contributor
Posts: 155

Re: Reading BIGINT data from teradata

Thanks a lot for the details Jaap. I will check out the DS2 option

Trusted Advisor
Posts: 1,056

Re: Reading BIGINT data from teradata

I've encountered this issue with Sybase IQ. It's not an easy one to resolve.

First, what is the nature of your BIGINT column? Is it something where losing four digits at the right will cause a problem? If not, just CAST it to a floating point column, and treat it normally in SAS.

You could check to see if any of the contents exceed the maximum integer value that can be processed by SAS. If not, you won't lose any precision, and you're good to go.

If the contents isn't intended to be treated as a number (for example a customer ID or something similar), instead you can convert it to a character variable of the appropriate length, and then treat it normally in SAS.

If you genuinely do need to treat it as a nineteen-digit number in SAS, that will be very tricky. Perhaps there's a way to use DS2 to do this, but I don't have any experience in that area.

Tom

Regular Contributor
Posts: 155

Re: Reading BIGINT data from teradata

Thanks a lot for the details Tom.

Yes, the column is an identifier for customer so I will try out the option for converting to character while extracting into SAS and reconverting it into numeric and storing in sas.

Trusted Advisor
Posts: 1,056

Re: Reading BIGINT data from teradata

I would suggest you don't convert it to numeric in SAS. Just use it as a character field.

Regular Contributor
Posts: 155

Re: Reading BIGINT data from teradata

ok...but due to some constraints, I am not supposed to alter the column data type. That's the reason why I decided to convert back.

Trusted Advisor
Posts: 1,056

Re: Reading BIGINT data from teradata

Dear Sheeba; succinctly, DON'T DO IT!

You simply can't reliably convert a number bigger than 15 digits into the SAS numeric format. You will absolutely, definitely, lose accuracy on some of the numbers.

Give this code a spin:

data have;

length CharNum $16;

input CharNum;

cards;

9876543210987654

9876543210987653

9876543210987652

9876543210987651

9876543210987650

9876543210987659

9876543210987658

9876543210987657

9876543210987656

9876543210987655

9876543210987654

run;

proc print; run;

data convert1;

set have;

NumericNum = input(CharNum, 16.);

format NumericNum z16.;

run;

proc print; run;

data convert2;

set convert1;

ConvertedCharNum = put(NumericNum, z16.);

run;

proc print; run;

Tom

P.S. the only option is to explore bigint in DS2, which I haven't done.

Regular Contributor
Posts: 155

Re: Reading BIGINT data from teradata

Thanks for the details Tom. I will explore DS2

Respected Advisor
Posts: 3,837

Re: Reading BIGINT data from teradata

You need to be on SAS9.4 in order to use DS2. It would allow you to deal directly with a data type of BIGINT but you still wouldn't be able to store this bigint as a numeric variable in a SAS file without loosing precision. DS2 gets its full power by in-database processing - but in order to do this the SAS in-database code accelerator must be installed on the Teradata side: SAS(R) 9.4 DS2 Language Reference, Second Edition

As others already suggested: If you need to pull your data into SAS then I believe the only way to deal with this BIGINT is to cast it to a character. If you're using implicit pass-through then you could try and use the DBSASTYPE dataset option SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition and leave the conversion to the SAS/Access engine.

I've never done this for BIGINT so you would need to try if this works (let us know the outcome if you do so).

"due to some constraints, I am not supposed to alter the column data type"

I can understand this principle but as there is no 1:1 match of data types between Teradata and SAS and you have a very valid reason why you must change the type, I believe this principle doesn't apply.

Regular Contributor
Posts: 155

Re: Reading BIGINT data from teradata

tnx Patrick.... I will look for casting into character

Contributor
Posts: 22

Re: Reading BIGINT data from teradata

This issue is a big concern because we are migrating our SOR Oracle to Teradata.

The BIGINT problem occurs when you try to put the data into a SAS dataset, because SAS does not support BIGINT data.  SAS will complain that it is not a supported data type, and will remove the column(s) from the dataset or the result.

If you are using the libname access method, then you should be able to use the "cast=yes" option on your libname or dataset or your DIS table.  That feature is currently broken  in out 9.4 version, but theoretically it should work.  The documentation for cast=yes is in the SAS/Access user guide, and yes, they convert it to float.

If you are using pass-through SQL, you have no automatic options for handling BIGINT data.  The casting to character(20) takes up 2.5 times space, and one of our tables has 27 BIGINT columns.  In Teradata, if you cast your data to DECIMAL(15) -- which is recommended in Teradata documents -- then you will get a Teradata error if the data does not fit.  The error is a good thing, since it prevents you from producing inaccurate results.  SAS turns that BIGINT data into a number with 16. format, which is OK if you have a host that can represent 15 digits plus a sign.  Our Solaris 64-bit OS can represent the number, but you should verify that your host can represent that data.  In any case, you can create a test to be sure that 15 digits are represented.  I saw one test posted, but here's what I would do:

  1. Create a Teradata table with numbers from 999999999990000 to 999999999999999
  2. Cast each number both as char(20) and as decimal(15)
  3. Create a dataset of this Teradata data in SAS by using pass-through SQL.
  4. Count the distinct occurrences of each column in the table.
  5. The counts should be exactly equal if you can represent 15 digits of data.

Has anyone passed SAS macros or functions to Teradata, so that you can use them in pass-through SQL?  It would be grand to have %bigint(foo) return a result like "(cast foo as decimal(15)) as foo".

Concerning the comment on DS2:

DS2 exists in SAS 9.3, but it became official in SAS 9.4.

I tried it out, and it produced some results in SAS 9.3.

Respected Advisor
Posts: 3,063

Re: Reading BIGINT data from teradata

There should be no problem using SAS macros to generate Teradata passthru SQL. This happens within SAS though and the end result is valid SQL which is passed on to Teradata. I use the approach with SQL Server.

Super User
Super User
Posts: 6,351

Re: Reading BIGINT data from teradata

Then tell the creator of the database to convert their customer id field to a character field.  There is no reason to use a number for a field that will never be used in mathematical operations.

Trusted Advisor
Posts: 1,056

Re: Reading BIGINT data from teradata

I second this! You will have problems not only with SAS, but with any other facilities that don't support an 8 byte INT datatype. It should be stored as character.

Tom

Ask a Question
Discussion stats
  • 18 replies
  • 4722 views
  • 6 likes
  • 7 in conversation