04-17-2014 10:46 PM
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,
04-18-2014 02:35 AM
check and understand the datatypes as first step. See the notes about precision on floating numbers with SAS.
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
04-18-2014 10:04 AM
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.
04-21-2014 12:50 PM
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.
04-22-2014 02:44 PM
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:
length CharNum $16;
proc print; run;
NumericNum = input(CharNum, 16.);
format NumericNum z16.;
proc print; run;
ConvertedCharNum = put(NumericNum, z16.);
proc print; run;
P.S. the only option is to explore bigint in DS2, which I haven't done.
04-23-2014 07:57 PM
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.
12-02-2014 01:36 PM
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:
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.
12-03-2014 01:54 PM
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.
04-23-2014 07:08 PM
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.
04-23-2014 08:22 PM
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.