SAS/ACCESS Software for Relational Databases: Reference

Teradata Data Types

Every column in a table has a name and data type. The data type tells Teradata how much physical storage to set aside for the column, as well as the form in which to store the data. Teradata data types fall into categories: binary, character, date, and numeric data. Each type is described in the following sections.

Note: SAS/ACCESS Version 8 does not support the following Teradata data types: GRAPHIC, VARGRAPHIC and LONG VARGRAPHIC. [cautionend]

Binary String Data

BYTE (n): specifies a fixed-length column of length n for binary string data. The maximum for n is 64,000.
VARBYTE (n): specifies a varying-length column of length n for binary string data. The maximum for n is 64,000.

Character String Data

CHAR (n): specifies a fixed-length column of length n for character string data. The maximum for n is 64,000.
VARCHAR (n): specifies a varying-length column of length n for character string data. The maximum for n is 64,000. VARCHAR is also known as CHARACTER VARYING.
LONG VARCHAR: specifies a varying-length column, of the maximum length, for character string data. LONG VARCHAR is equivalent to VARCHAR(32000) or VARCHAR(64000) depending on which Teradata version your server is running.

Date Data

The date type in Teradata is similar to the SAS date value. It is stored internally as a numeric value and is displayed in a site-defined format. The Teradata date type that SAS supports is listed here.

Note: Date type columns may contain Teradata values that are out of range for the SAS System, which handles dates from 1582 A.D. through 20,000 A.D. If SAS/ACCESS encounters an unsupported date, for example, a date earlier than 1582 A.D., it will return an error message and display the date as a missing value. [cautionend]

DATE: specifies dates values in the default format YYYY-MM-DD. For example, January 25, 1989, is input as 1989-01-25. Values for this type can range from 0001-01-01 through 9999-12-31.

Numeric Data

Note: When reading Teradata data, SAS/ACCESS converts all Teradata numeric data types to the SAS System internal format, floating-point. [cautionend]

BYTEINT

specifies a single-byte signed binary integer. Values can range from -128 to +127.

DECIMAL(n,m)

specifies a packed-decimal number. n is the total number of digits (precision). m is the number of digits to the right of the decimal point (scale). The range for precision is 1 through 18. The range for scale is 0 through n.

If m is omitted, 0 is assigned and n can also be omitted. Omitting both n and m results in the default DECIMAL(5,0). DECIMAL is also known as NUMERIC.

CAUTION:: Because SAS stores numbers in floating-point format, a Teradata DECIMAL number with very large precision can lose precision. For example, when SAS/ACCESS running on a UNIX MP-RAS client reads a Teradata column specified as DECIMAL (18,18), it maintains only 13 digits of precision. This can cause problems. A large DECIMAL number can cause the WHERE clause that SAS/ACCESS generates to perform improperly (fail to select the expected rows). There are other potential problems. For this reason, use carefully large precision DECIMAL data types for Teradata columns that SAS/ACCESS will access.

FLOAT

specifies a 64-bit IEEE floating-point number in sign-and-magnitude form. Values can range from approximately 2.226 x 10 ^-308 to 1.797 x 10³⁰⁸. FLOAT is also known as REAL or DOUBLE PRECISION.

Note: When the SAS/ACCESS client internal floating point format is IEEE, Teradata FLOAT numbers convert precisely to SAS numbers. Exact conversion applies to SAS/ACCESS for Teradata running under UNIX MP-RAS. However, if you are running SAS/ACCESS for Teradata under OS/390, there can be minor precision and magnitude discrepancies. [cautionend]

INTEGER

specifies a large integer. Values can range from -2,147,483,648 through +2,147,483,647.

SMALLINT

specifies a small integer. Values can range from -32,768 through +32,767.

NULL and NOT NULL Values

The NULL value means an absence of information in Teradata and is analogous to the SAS System's missing value. Therefore, when SAS/ACCESS reads a Teradata NULL value, it generates a SAS missing value.

By default, Teradata columns accept NULL values. But, you can define columns so that they will not contain NULL values. To create Teradata columns that disallow NULL values, use the DBNULL= data set option. For example, when creating a SALES table, define the CUSTOMER column NOT NULL, telling Teradata not to add a row to the table unless the CUSTOMER column for the row has a value. For more information, as well as the syntax, see DBNULL=.

Chapter Contents
Previous
Next
Top of Page