Chapter Contents

Previous

Next

LIBNAME Statement Data Conversions

When you read a Teradata table, SAS/ACCESS assigns default SAS data types and formats to the Teradata table columns. In assigning the defaults SAS/ACCESS does not use Teradata's column format information. The Default SAS Formats shows the default SAS System formats that the LIBNAME statement assigns to the Teradata data types.

Note:   The Teradata data types, GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC, are not supported by Version 8 of SAS/ACCESS.  

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 Default Output Teradata Data Types table shows the default Teradata data types that the LIBNAME statement assigns to the SAS System formats during output processing.

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

Default SAS Formats

Teradata Data Type	Default SAS Format
CHAR(n )	$n. (n<= 32,767)
CHAR(n )	$32767.(n>32,767) 1
VARCHAR(n )	$n. (n<= 32,767)
VARCHAR(n )	$32767.(n> 32,767) 1
LONG VARCHAR(n )	$32767. 1
BYTE(n )	$HEXn. (n<= 32,767)
BYTE(n )1	$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 )2	(n+2 ).(m )
FLOAT	none
DATE3	DATE9.
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 "Numeric Data." 3 See "Date Data" for how SAS/ACCESS handles dates that are outside the valid SAS date range.

Default Output Teradata Data Types

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	A time format1	FLOAT
Numeric	A datetime format1	FLOAT
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 FLOAT is the default Teradata output type for SAS time and datetime values. If you want SAS/ACCESS to display Teradata columns that contain SAS time and datetimes properly, you must explicitly assign the appropriate SAS time or datetime display format to the column. If you do not assign a format, SAS/ACCESS displays the value as a simple floating point number; it does not look like a time or datetime value.

Example: Output of a Teradata Table with Date and Datetime Columns

  /* Outputs a Teradata table named sasdt        */
  /* The dt and t columns default to Teradata    */
  /* FLOAT values.                               */
libname tra teradata user=kamdar password=ellis;
data tra.sasdt; 
  format dt datetime26.6;
         dt = '04jul1976:12:00:00.00000'dt;
  format t time16.6;
         t = '12:32:59.67765't;
run;

The PROC print statements that follow display the Teradata table. The first PROC PRINT statement displays the DT and T columns as FLOAT values. This is not very useful. The second PROC PRINT statement assigns formats to the Teradata DT and T columns. As a result, SAS displays the columns properly, as SAS date and datetime values.

  /*PROC PRINT Statement With No Assigned Formats*/
 proc print data=tra.sasdt;
 run;

Sample PROC PRINT Display 1

Obs dt t 1 520948800 45179.68

  /*PROC PRINT Statement with Explicit Date/Datetime Formats*/
 proc print data=tra.sasdt;
  format dt datetime26.6 t time16.6;
 run;

Sample PROC PRINT Display 2

Obs dt t 1 04JUL1976:12:00:00.000000 12:32:59.677650

Chapter Contents

Previous

Next

Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.