DBSASTYPE= Data Set Option

Specifies data types to override the default SAS data types during input processing.

Valid in:	DATA and PROC steps
Default:	DBMS-specific
Supports:	All

Syntax

Syntax Description

Details

Examples

Example 1: Overriding the Default Data Type

Example 2: Converting Column Length

Example 3: Appending Tables to Match Data Types

Syntax

DBSASTYPE= (column-name-1=<'> SAS-data-type<'> <…column-name-n=<'> SAS-data-type<'> > )

Syntax Description

column-name

specifies a DBMS column name.

SAS-data-type

specifies a SAS data type. SAS data types include the following: CHAR(n), NUMERIC, DATETIME, DATE, TIME. See the documentation for your database for details.

Details

By default, the LIBNAME engine converts each DBMS data type to a SAS data type during input processing. When you need a different data type, you can use this option to override the default and assign a SAS data type to each specified DBMS column. Some conversions might not be supported. If a conversion is not supported, SAS prints an error to the log.

Examples

Example 1: Overriding the Default Data Type

In the following example, DBSASTYPE= specifies a data type to use for the column MyColumn when SAS is printing ODBC data. If the data in this DBMS column is stored in a format that SAS does not support, such as SQL_DOUBLE(20), using this data set option enables SAS to print the values.

proc print data=mylib.mytable
   (dbsastype=(mycolumn='CHAR(20)'));
run;

Example 2: Converting Column Length

In the following example, the data that is stored in the DBMS FiberSize column has a data type that provides more precision than what SAS could accurately support, such as DECIMAL(20). If you used only PROC PRINT on the DBMS table, the data might be rounded or displayed as a missing value. Instead, you could use DBSASTYPE= to convert the column to a character field of the length 21. Because the DBMS performs the conversion before the data is brought into SAS, there is no loss of precision.

proc print data=mylib.specprod
   (dbsastype=(fibersize='CHAR(21)'));
run;

Example 3: Appending Tables to Match Data Types

The following example uses DBSASTYPE= to append one table to another when the data types are not comparable. If the SAS data set has a column EmpId defined as CHAR(20) and the DBMS table has an EmpId column defined as DECIMAL (20), you can use DBSASTYPE= to make them match:

proc append base=dblib.hrdata (dbsastype=(empid='CHAR(20)'))
            data=saslib.personnel;
run;

DBSASTYPE= specifies to SAS that the EmpId is defined as a character field of length 20. When a row is inserted from the SAS data set into a DBMS table, the DBMS performs a conversion of the character field to the DBMS data type DECIMAL(20).