How do to access a bigint in Teradata from Enterprise Guide?

[bwasicak]

There are several fields in my Teradata installation which are BIGINT, in EG these fields do not ever show up.  I have to do a data describe on each table to see the error in the log to find the missing fields.  Is there a SAS Access engine patch to fix this?  I also need to know when SAS will be able to read Teradata table and column names greater than 30 or 32 characters in length.

[LinusH]

According to SAS/ACCESS doc, bigint is not supported, hence nor showing up when using libname.

You could go around this by using SQL pass-thru and cast bigint values to a SAS supported data type.

For a normal analyst this could be a complicated operation. Sp you should talk to your TD DBAs an ask them to create views with datatypes supported by SAS.

The same solution apply to your second requirement.

[SASKiwi]

There are suggestions on the forum regarding enhancing SAS to better read table names and columns greater than 32 characters from external databases in general - not just Teradata. Table names are the most problematic as the only workaround to read them is to use SQL passthru. Long column names can usually be read by most methods and are simply truncated to 32 characters and are automatically suffixed with a number if they don't remain unique.

I too would be interested in hearing about any plans SAS may have in this area. To date I have not heard of any.

[JBailey · Accepted solution]

Hi Bwasicak,

SAS cannot read the BIGINT data types. There are a couple of ways to handle it.

The first is to have your DBA create views which CAST the BIGINT columns to a character data type (@LinusH's recommendation). You can do this, without creating Teradata views, using explicit pass-thru.

I have pulled some slides covering using SAS with Teradata BIGINT from a workshop that I used to deliver. Hopefully, they will help. If the slides require clarification please let me know.

Be careful with TRUNCATE_BIGINT. Doing calculations using BIGINT columns could very well be problematic because you can get incorrect answers.

http://support.sas.com/kb/39/831.html

[SASKiwi]

Good to hear handling of long table names are being looked at!

[mebass]

Just updating to let folks know that the longer table and column names (32 chars) are supported with SAS/ACCESS Interface to Teradata as of SAS 9.4, maintenance release 3, which came out this past summer.

[LinusH]

Nice, one step forward.
Now we just wait for SAS to support object names longer than 32 chars...

[LorenSylvan]

Hello - has any of this changed in the past year, or is this the recommended way to get bigint values into SAS, along with the limitations and caveats? 

Thanks

Loren

[LinusH]

No