BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bwasicak
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
JBailey
Barite | Level 11

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.

Teradata_BIGINT_01.jpg

 

 

 

Teradata_BIGINT_02.jpg

 

 

Teradata_BIGINT_03.jpg

 

 

Teradata_BIGINT_04.jpg

 

 

Teradata_BIGINT_05.jpg

 

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

View solution in original post

8 REPLIES 8
LinusH
Tourmaline | Level 20

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.

Data never sleeps
SASKiwi
PROC Star

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
Barite | Level 11

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.

Teradata_BIGINT_01.jpg

 

 

 

Teradata_BIGINT_02.jpg

 

 

Teradata_BIGINT_03.jpg

 

 

Teradata_BIGINT_04.jpg

 

 

Teradata_BIGINT_05.jpg

 

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
PROC Star

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

mebass
SAS Employee
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
Tourmaline | Level 20
Nice, one step forward.
Now we just wait for SAS to support object names longer than 32 chars...
Data never sleeps
LorenSylvan
SAS Employee

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
Tourmaline | Level 20
No
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 6808 views
  • 0 likes
  • 6 in conversation