SAS/ACCESS Interface to Teradata

Data Types for Teradata

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. This section includes information about Teradata data types, null values, and data conversions.

SAS/ACCESS 9 does not support these Teradata data types: GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC.

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, Time, and Timestamp Data

The date type in Teradata is similar to the SAS date value. It is stored internally as a numeric value and displays in a site-defined format. Date type columns might contain Teradata values that are out of range for SAS, which handles dates from A.D. 1582 through A.D. 20,000. If SAS/ACCESS encounters an unsupported date (for example, a date earlier than A.D. 1582), it returns an error message and displays the date as a missing value.

See Using TIME and TIMESTAMP for examples.

The Teradata date/time types that SAS supports are listed here.

DATE

specifies date 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.

TIME (n)

specifies time values in the format HH:MM:SS.SS. In the time, SS.SS is the number of seconds ranging from 00 to 59 with the fraction of a second following the decimal point.

n is a number from 0 to 6 that represents the number of digits (precision) of the fractional second. For example, TIME(5) is 11:37:58.12345 and TIME(0) is 11:37:58. This type is supported for Teradata Version 2, Release 3 and later.

TIMESTAMP (n)

specifies date/time values in the format YYYY-MM-DD HH:MM:SS.SS. In the timestamp, SS.SS is the number of seconds ranging from 00 through 59 with the fraction of a section following the decimal point.

n is a number from 0 to 6 that represents the number of digits (precision) of the fractional second. For example, TIMESTAMP(5) is 1999-01-01 23:59:59.99999 and TIMESTAMP(0) is 1999-01-01 23:59:59. This type is supported for Teradata Version 2, Release 3 and later.

CAUTION:: When processing WHERE statements (using PROC SQL or the DATA step) that contain literal values for TIME or TIMESTAMP, the SAS engine passes the values to Teradata exactly as they were entered, without being rounded or truncated. This is done so that Teradata can handle the rounding or truncation during processing.

Numeric Data

When reading Teradata data, SAS/ACCESS converts all Teradata numeric data types to the SAS internal format, floating-point.

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 high 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 accesses.

FLOAT

specifies a 64-bit Institute of Electrical and Electronics Engineers (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.

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 Interface to Teradata running under UNIX MP-RAS. However, if you are running SAS/ACCESS Interface to Teradata under z/OS, there can be minor precision and magnitude discrepancies.

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.

Teradata Null Values

Teradata has a special value that is called NULL. A Teradata NULL value means an absence of information and is analogous to a SAS missing value. When SAS/ACCESS reads a Teradata NULL value, it interprets it as a SAS missing value.

By default, Teradata columns accept NULL values. However, you can define columns so that they do not contain NULL values. For example, when you create a SALES table, define the CUSTOMER column as NOT NULL, telling Teradata not to add a row to the table unless the CUSTOMER column for the row has a value. When creating a Teradata table with SAS/ACCESS, you can use the DBNULL= data set option to indicate whether NULL is a valid value for specified columns.

For more information about how SAS handles null values, see Potential Result Set Differences When Processing Null Data.

To control how SAS missing character values are handled by Teradata, use the NULLCHAR= and NULLCHARVAL= data set options.

LIBNAME Statement Data Conversions

This table shows the default formats that SAS/ACCESS Interface to Teradata assigns to SAS variables when using the LIBNAME statement to read from a Teradata table. SAS/ACCESS does not use Teradata table column attributes when it assigns defaults.

Default SAS Formats for Teradata
Teradata Data Type	Default SAS Format
CHAR(n )	$n (n<= 32,767)
CHAR(n )	$32767.(n>32,767) ¹
VARCHAR(n )	$n (n<= 32,767)
VARCHAR(n )	$32767.(n> 32,767) ¹
LONG VARCHAR(n )	$32767. ¹
BYTE(n )	$HEXn. (n<= 32,767)
BYTE(n )¹	$HEX32767.(n> 32,767)
VARBYTE(n )	$HEXn. (n<= 32,767)
VARBYTE(n )	$HEX32767.(n> 32,767)
INTEGER	11.0
SMALLINT	6.0
BYTEINT	4.0
DECIMAL(n, m )²	(n+2 ).(m )
FLOAT	none
DATE³	DATE9.
TIME(n)⁴	for n=0, TIME8. for n>0, TIME9+n.n
TIMESTAMP(n)⁴	for n=0, DATETIME19. for n>0, DATETIME20+n.n
TRIM(LEADING FROM c)	LEFT(c)
CHARACTER_LENGTH(TRIM(TRAILING FROM c)	LENGTH(c)
(v MOD d)	MOD(v,d)
TRIMN(c)	TRIM(TRAILING FROM c)
1 When reading Teradata data into SAS, DBMS columns that exceed 32,767 bytes are truncated. The maximum size for a SAS character column is 32,767 bytes. 2 If the DECIMAL number is extremely large, SAS can lose precision. For details, see the topic "Numeric Data". 3 See the topic "Date/Time Data" for how SAS/ACCESS handles dates that are outside the valid SAS date range. 4 TIME and TIMESTAMP are supported for Teradata Version 2, Release 3 and later. The TIME with TIMEZONE, TIMESTAMP with TIMEZONE, and INTERVAL types are presented as SAS character strings, and thus are harder to use.

When you create Teradata tables, the default Teradata columns that SAS/ACCESS creates are based on the type and format of the SAS column. The following table shows the default Teradata data types that SAS/ACCESS assigns to the SAS formats during output processing when you use the LIBNAME statement.

Default Output Teradata Data Types
SAS Data Type	SAS Format	Teradata Data Type
Character	$w. $CHARw. $VARYINGw.	CHAR[w]
Character	$HEXw.	BYTE[w]
Numeric	A date format	DATE
Numeric	TIMEw.d	TIME(d)¹
Numeric	DATETIMEw.d	TIMESTAMP(d)¹
Numeric	w.(w2)	BYTEINT
Numeric	w.(3w4)	SMALLINT
Numeric	w.(5w9)	INTEGER
Numeric	w.(w10)	FLOAT
Numeric	w.d	DECIMAL(w-1,d)
Numeric	All other numeric formats	FLOAT
1 For Teradata Version 2, Release 2 and earlier, FLOAT is the default Teradata output type for SAS time and datetime values. To display Teradata columns that contain SAS times and datetimes properly, you must explicitly assign the appropriate SAS time or datetime display format to the column.

To override any default output type, use the DBTYPE= data set option.

Data Returned as SAS Binary Data with Default Format $HEX

	BYTE
	VARBYTE
	LONGVARBYTE
	GRAPHIC
	VARGRAPHIC
	LONG VARGRAPHIC

Top of Page