SAS can hold only 15 significant digits whereas Teradata's BIGINT data type can hold 18 significant digits. As a result, this causes a problem with BIGINT data types within SAS.

The best way to handle columns of type BIGINT is dependent upon several factors:

• Are any of your BIGINT columns being used for computations? If so, will the column ever contain a number that is greater than 15 digits?

  If none of your BIGINT columns will ever contain a number that is greater than 15 digits, then you can set the environment variable TRUNCATE_BIGINT=YES, which enables BIGINT support with truncation. When this environment variable is set to YES, all of your BIGINT columns are truncated to 15 digits. Caution: You should specify TRUNCATE_BIGINT=YES only if the data that is contained in your columns are 15 digits or fewer.

• If none of your BIGINT columns are used for computations and are used only for character fields (such as ID numbers), then you can use the DBSASTYPE= option to specify what data type should be used when data is read into SAS. For BIGINT data in SAS, you typically use a character data type because SAS does not have a data type that can show such large numbers. The following example illustrates the use of the DBSASTYPE= option as a SAS data set option with the LIBNAME engine:

   

  libname mylib teradata user=user–name password=password server=server–name;
  
  data temp1;
     set mylib.bigclass (dbsastype= (name='char(20)'));
  run;

You must use casting when you use explicit SQL Pass-Through processing. However, you can use TERADATA views to cast the BIGINT data type to the CHAR data type. You do not have to change your existing SQL. It is only necessary to create a view with the BIGINT fields cast as CHAR, and then use the view in your SQL, as shown in the following example:

Casting the BIGINT as a data type INT is not recommended. Doing so causes numeric rounding to occur for large values. You should not try to use a BIGINT type that you are casting as any numeric data type within SAS to do any calculations.

If you prefer, you can create a view of the Teradata table with the cast to CHAR(20) embedded in the SELECT statement in the view. Then you can use the SQL procedure and SELECT statement to select from any SAS data set.

You can create Teradata views from within Teradata with syntax similar to the following:

To get a better understanding of numeric precision, see SAS KB0036211, "Choosing your degree of numeric precision."

Click the Hot Fix tab in this note to access the hot fix for this issue.

After you install the hot fix, you must set the environment variable TRUNCATE_BIGINT to YES to enable processing of BIGINT data types by the SAS/ACCESS Interface to Teradata engine.

Operating System and Release Information

A fix for this issue for SAS/ACCESS Interface to Teradata 9.21_M1 is available at: