Data Types for Teradata

Supported Teradata Data Types

Here are the data types that SAS/ACCESS Interface to Teradata supports:

• Binary string data:

  BYTE(n)

  VARBYTE(n)

• Character string data:

  CHAR (n)

  VARCHAR (n)

  LONG VARCHAR

• Date and time data:

  DATE

  TIME(n)

  TIMESTAMP (n)

  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.

  Note: 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. The values are passed without being rounded or truncated. This is done so that Teradata can handle the rounding or truncation during processing.
• Numeric data:

  BYTEINT	INTEGER
  DECIMAL(n,m)	NUMBER
  FLOAT | REAL | DOUBLE PRECISION	SMALLINT

  Note: When performing calculations on numeric values and when storing numeric values, SAS maintains up to 15 digits of precision. When you read values that contain more than 15 decimal digits of precision from a database into SAS, the values that SAS reads are rounded to meet this condition. When you use a large numeric value in a WHERE clause, this rounding can cause unexpected results, such as not selecting desired rows. For noncomputational purposes, such as storing ID values or credit card numbers, you can read the data in as character data. For more information, see Your Options When Choosing Your Needed Degree of Precision.

For details about these data types, see your Teradata documentation.

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 specify columns so that they do not contain NULL values. For example, when you create a SALES table, specify the CUSTOMER column as NOT NULL. This tells 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, 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.

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.

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

Working with NUMBER Data

proc sql;
   connect to teradata(user=myuser pass=myPwd
                       server=myServer);
   select * from connection to Teradata(select col1, cast(col2 as FLOAT), col3
      from TDlib.table);
   ... additional code ...
quit;

proc fedsql;
    create table tera.test(col1 numeric(4,2));
    insert into tera.test values(12.34);
    select * from tera.test;
quit;

proc ds2;
data test;
  dcl numeric col1(4,2);
  method=run();
     Col1=12.34; output;
  end;
endData;
run;
quit; 

Data Returned as SAS Binary Data with Default Format $HEX

• BYTE
• VARBYTE
• LONGVARBYTE
• GRAPHIC
• VARGRAPHIC
• LONG VARGRAPHIC