DBSASTYPE= Data Set Option

Specifies data types to override the default SAS data types during input processing.
Valid in: DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
Default: DBMS-specific
Data source: Aster nCluster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase IQ, Teradata

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, which can be CHAR(n), NUMERIC, DATETIME, DATE, TIME. See the DBMS-specific reference section for your SAS/ACCESS interface for details.

Details

By default, the SAS/ACCESS interface for your DBMS 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. In that case, SAS prints an error to the log.

Examples

Example 1: Override the Default Data Type

In this example, DBSASTYPE= specifies a data type to use for the MYCOLUMN column when SAS prints ODBC data. SAS can print the values if the data in this DBMS column is stored in a format that SAS does not support, such as SQL_DOUBLE(20).
proc print data=mylib.mytable
   (dbsastype=(mycolumn='CHAR(20)'));
run;

Example 2: Convert Column Length

In the next example, 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 use only PROC PRINT on the DBMS table, the data might be rounded or are displayed as a missing value. So you could use DBSASTYPE= instead to convert the column so that the length of the character field is 21. The DBMS performs the conversion before the data is brought into SAS, so precision is preserved.
proc print data=mylib.specprod
   (dbsastype=(fibersize='CHAR(21)'));
run;

Example 3: Append Tables to Match Data Types

The next example uses DBSASTYPE= to append one table to another when the data types cannot be compared. If the EMPID variable in the SAS data set is defined as CHAR(20) and the EMPID column in the DBMS table is 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).