CAST= LIBNAME Option

Specifies whether SAS or the Teradata DBMS server performs data conversions.

Valid in:	SAS/ACCESS LIBNAME statement
Default:	none
Data source:	Teradata
See:	CAST= data set option, CAST_OVERHEAD_MAXPERCENT= LIBNAME option, CAST_OVERHEAD_MAXPERCENT= data set option

Syntax

Details

Examples

Example 1: Force Casting for All Tables

Example 2: Turn Casting Off for a Specific Table Reference

Syntax

CAST=YES | NO

Syntax Description

YES: forces data conversions (casting) to be done on the Teradata DBMS server and overrides any data overhead percentage limit.

NO: forces data conversions to be done by SAS and overrides any data overhead percentage limit.

Details

Internally, SAS numbers and dates are floating-point values. Teradata has varying formats for numbers, including integers, floating-point values, and decimal values. Number conversion must occur when you are reading Teradata numbers that are not floating point (Teradata FLOAT). SAS/ACCESS can use the Teradata CAST= function to cause Teradata to perform numeric conversions. The parallelism of Teradata makes it suitable for performing this work. This is especially true when running SAS on z/OS, where CPU activity can be costly.

CAST= can cause more data to be transferred from Teradata to SAS, as a result of the option forcing the Teradata type into a larger SAS type. For example, the CAST= transfer of a Teradata BYTEINT to SAS floating point adds seven overhead bytes to each row transferred.

These Teradata types are candidates for casting.

INTEGER
BYTEINT
SMALLINT
DECIMAL
DATE

SAS/ACCESS limits data expansion for CAST= to 20% to trade rapid data conversion by Teradata for extra data transmission. If casting does not exceed a 20% data increase, all candidate columns are cast. If the increase exceeds this limit, SAS attempts to cast only Teradata DECIMAL types. If casting only DECIMAL types still exceeds the increase limit, SAS performs the data conversions.

You can alter the casting rules by using the CAST= or CAST_OVERHEAD_MAXPERCENT= LIBNAME option. With CAST_OVERHEAD_MAXPERCENT=, you can change the 20% overhead limit. With CAST=, you can override the percentage rules.

CAST=YES forces Teradata to cast all candidate columns.
CAST=NO cancels all Teradata casting.

CAST= applies only when you are reading Teradata tables into SAS, not when you are writing Teradata tables from SAS.

Also, CAST= applies only to SQL that SAS generates for you. If you supply your own SQL with the explicit SQL feature of PROC SQL, you must code your own casting clauses. Data conversions are therefore forced to occur in Teradata instead of SAS.

Examples

Example 1: Force Casting for All Tables

This example demonstrates the use of the CAST= option in a LIBNAME statement to force casting for all referenced tables.

libname mydblib teradata user=testuser pw=testpass cast=yes;
proc print data=mydblib.emp;
where empno<1000;
run;
proc print data=mydblib.sal;
where salary>50000;
run;

Example 2: Turn Casting Off for a Specific Table Reference

This example demonstrates the use of the CAST= option in a table reference to turn casting off for that table.

proc print data=mydblib.emp (cast=no);
where empno<1000;
run;