Usage Note 66794: A WHERE clause for a Teradata BIGINT column generates the message "...Precision error in FLOAT type constant or during implicit conversions..."
 |  |  |  |  |
SAS® has limited support for BIGINT data types in Teradata when you use the TRUNCATE_BIGINT environment variable and the SAS data set option DBSASTYPE, as shown in this syntax example:
LIBNAME my-libref TERADATA SERVER=my-server DATABASE=my-database USER=my-user PW=my-password ;
PROC SQL ;
SELECT *
FROM my-libref.my-table(DBSASTYPE=(my-BigInt='CHAR(20)'))
WHERE my-BigInt='1234567890' ;
QUIT ;
- TRUNCATE_BIGINT=YES enables SAS to work with Teradata BIGINT columns, but it can lead to a loss of precision for the numeric values.
- The DBSASTYPE option results in the BIGINT columns being cast or converted to character strings in Teradata, and they are returned to SAS as a string with no loss of precision.
However, if the value that you use in the WHERE clause is too long, typically more than 15 digits, Teradata generates an error, because it cannot implicitly convert the string passed from SAS into a Teradata FLOAT data type.
A message similar to the following error might appear:
Click the Hot Fix tab in this note to access the hot fix for this issue.
Install the fix to enable additional support for Teradata BIGINT data types, which include these types of support:
- Support for BIGINT literal values that are longer than 15 digits
- Support for pass-through of some joins and subqueries when BIGINT columns are involved
- Support for the MAX, MIN, and COUNT functions when used on BIGINT columns
After the fix is installed, you must also set the environment variables TRUNCATE_BIGINT and SQL_IP_BIGINT as shown here:
options set=SQL_IP_BIGINT YES ;
You also need to continue to use the DBSASTYPE data set option for all BIGINT columns with values longer than 15 digits.
For BIGINT columns with values that are 15 digits or less, refer to SAS Note 39831, "Issues with BIGINT data types with SAS/ACCESS® Interface to Teradata."
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | Base SAS | z/OS | 9.4_M6 | 9.4 TS1M6 | ||
| z/OS 64-bit | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft® Windows® for x64 | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft Windows 8 Enterprise 32-bit | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft Windows 8 Enterprise x64 | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft Windows 8 Pro 32-bit | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft Windows 8 Pro x64 | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft Windows 8.1 Enterprise 32-bit | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft Windows 8.1 Enterprise x64 | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft Windows 8.1 Pro 32-bit | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft Windows 8.1 Pro x64 | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft Windows 10 | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft Windows Server 2008 | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft Windows Server 2008 R2 | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft Windows Server 2008 for x64 | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft Windows Server 2012 Datacenter | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft Windows Server 2012 R2 Datacenter | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft Windows Server 2012 R2 Std | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft Windows Server 2012 Std | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft Windows Server 2016 | 9.4_M6 | 9.4 TS1M6 | ||||
| Microsoft Windows Server 2019 | 9.4_M6 | 9.4 TS1M6 | ||||
| Windows 7 Enterprise 32 bit | 9.4_M6 | 9.4 TS1M6 | ||||
| Windows 7 Enterprise x64 | 9.4_M6 | 9.4 TS1M6 | ||||
| Windows 7 Home Premium 32 bit | 9.4_M6 | 9.4 TS1M6 | ||||
| Windows 7 Home Premium x64 | 9.4_M6 | 9.4 TS1M6 | ||||
| Windows 7 Professional 32 bit | 9.4_M6 | 9.4 TS1M6 | ||||
| Windows 7 Professional x64 | 9.4_M6 | 9.4 TS1M6 | ||||
| Windows 7 Ultimate 32 bit | 9.4_M6 | 9.4 TS1M6 | ||||
| Windows 7 Ultimate x64 | 9.4_M6 | 9.4 TS1M6 | ||||
| 64-bit Enabled AIX | 9.4_M6 | 9.4 TS1M6 | ||||
| 64-bit Enabled Solaris | 9.4_M6 | 9.4 TS1M6 | ||||
| HP-UX IPF | 9.4_M6 | 9.4 TS1M6 | ||||
| Linux for x64 | 9.4_M6 | 9.4 TS1M6 | ||||
| Solaris for x64 | 9.4_M6 | 9.4 TS1M6 | ||||
A fix for this issue for Base SAS 9.4_M7 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/I9R.html#66794A fix for this issue for SAS/ACCESS Interface to Teradata 9.47 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/J8F.html#66794A fix for this issue for SAS/ACCESS Interface to Teradata 9.46 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/F1K.html#66794| Type: | Usage Note |
| Priority: | |
| Topic: | Data Management ==> Access Data Management ==> Data Sources ==> External Databases ==> Teradata Data Management ==> Manipulation and Transformation ==> Numeric Precision SAS Reference ==> LIBNAME Engines |
| Date Modified: | 2021-02-23 20:54:16 |
| Date Created: | 2020-10-16 10:45:49 |