topic Re: Reading BIGINT data from teradata in SAS Enterprise Guide

Reading BIGINT data from teradata

Sheeba — Fri, 18 Apr 2014 02:46:34 GMT

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

Re: Reading BIGINT data from teradata

jakarman — Fri, 18 Apr 2014 06:35:13 GMT

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

Re: Reading BIGINT data from teradata

TomKari — Fri, 18 Apr 2014 14:04:43 GMT

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

Re: Reading BIGINT data from teradata

Sheeba — Mon, 21 Apr 2014 16:46:55 GMT

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

Re: Reading BIGINT data from teradata

Sheeba — Mon, 21 Apr 2014 16:50:26 GMT

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.

Re: Reading BIGINT data from teradata

TomKari — Mon, 21 Apr 2014 18:36:30 GMT

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

Re: Reading BIGINT data from teradata

Sheeba — Tue, 22 Apr 2014 17:22:18 GMT

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.

Re: Reading BIGINT data from teradata

jakarman — Tue, 22 Apr 2014 17:37:32 GMT

Sheeba when you try to convert bigint to SAS numeric you are going to lose precision as numeric in SAS =floating= that is an implied dataconversion with limitations.

When you replace the bigint type with the char-type you are not changing anything as the identifier of a customer is not meant to do calculations.

Trying to do calculating a mean and variance on those identifier numbers is meaningless.

Would you use Sepa banking-accountnumbers you cannot use bigint as that contain characters (in some countries).

This type of smart compression using numbers within technology is coming of the old hollerith age. I am sorry for that.

Re: Reading BIGINT data from teradata

TomKari — Tue, 22 Apr 2014 18:44:51 GMT

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.

Re: Reading BIGINT data from teradata

Sheeba — Wed, 23 Apr 2014 22:51:21 GMT

Thanks Jaap. I will try to get familiarized with DS2

Re: Reading BIGINT data from teradata

Sheeba — Wed, 23 Apr 2014 22:52:26 GMT

Thanks for the details Tom. I will explore DS2

Re: Reading BIGINT data from teradata

Tom — Wed, 23 Apr 2014 23:08:20 GMT

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.

Re: Reading BIGINT data from teradata

Patrick — Wed, 23 Apr 2014 23:57:56 GMT

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.

Re: Reading BIGINT data from teradata

TomKari — Thu, 24 Apr 2014 00:22:32 GMT

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

Re: Reading BIGINT data from teradata

Sheeba — Thu, 24 Apr 2014 21:05:17 GMT

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

Re: Reading BIGINT data from teradata

Sheeba — Thu, 24 Apr 2014 21:06:12 GMT

tnx Tom. I will definitely look forward for that option.

Re: Reading BIGINT data from teradata

Sheeba — Mon, 28 Apr 2014 17:58:13 GMT

Hi ,

I have figured another way for reading the bigint column from Teradata. It is possible to cast the column as decimal while making passthrough. Then it is required to merge the temporary table(output of passthrough ) to the original table which requires column addition..

Regards,

Sheeba Swaminathan

Re: Reading BIGINT data from teradata

RedPlanet — Tue, 02 Dec 2014 18:36:48 GMT

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:

Create a Teradata table with numbers from 999999999990000 to 999999999999999
Cast each number both as char(20) and as decimal(15)
Create a dataset of this Teradata data in SAS by using pass-through SQL.
Count the distinct occurrences of each column in the table.
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.

Re: Reading BIGINT data from teradata

SASKiwi — Wed, 03 Dec 2014 18:54:36 GMT

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.