Data Set Options for Relational Databases

DBSASTYPE= Data Set Option

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

Default value:	DBMS-specific
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	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
Syntax Description
Details
Examples

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 your SAS/ACCESS interface documentation 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

In this example, DBSASTYPE= specifies a data type to use for the column MYCOLUMN when SAS is printing 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;

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 display 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;

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).

Top of Page