DBSASTYPE=

DBSASTYPE=

Specifies data type(s) to override the default SAS data type(s) during input processing of data through OLE DB.

Default value: Option is not specified.

Option type: Data set only

Syntax
Details

Syntax

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

column-name: specifies a column name in a data source.
SAS-data-type: specifies a SAS data type, which can be one of the following: CHAR(n), NUMERIC, DATETIME, DATE, TIME.

Details

This option is valid only when you read data into SAS through OLE DB.

By default, the SAS/ACCESS Interface to OLE DB converts each OLE DB data type to a predetermined SAS data type when processing data through OLE DB. When you need a different data type, you can use DBSASTYPE= to override the default data type selected by the SAS/ACCESS engine. Any errors are written to the SAS log. This option applies to cases in which you insert data into or append data to a data source, such as a DBMS table.

In the following example, the data 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 just used a PROC PRINT on the DBMS table, the data might be rounded or displayed as a missing value. Instead, you could use the DBSASTYPE= option to convert the column to a character field of the length 21. Because the conversion is performed before the data is brought into SAS, there is no loss of precision.

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

You can also use the DBSASTYPE= option in cases where you are appending one data source table to another table and the data types are not comparable. If the SAS data set has a variable CITY defined as CHAR(20) and the table has a column defined as DECIMAL (20), you can use DBSASTYPE= to make them match:

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

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

See OLE DB Data Types for details about the default data types for OLE DB.

Chapter Contents
Previous
Next
Top of Page

Default value:	Option is not specified.
Option type:	Data set only