SAS/ACCESS Software for Relational Databases: Reference

OLE DB Data Types

A data source's columns in a table each have a name and a data type. The data type tells the data source how much physical storage to set aside for the column and the form in which the data are stored.

OLE DB Data Types and Default SAS Formats shows all of the data types and default SAS formats that are supported by the SAS/ACCESS engine for OLE DB. This table does not explicitly define the data types as they exist for each data source. It lists the types that each data source's data type might map to. For example, an INTEGER data type under DB2 might map to an OLE DB data type of DBTYPE_I4. All data types are supported.

OLE DB Data Types and Default SAS Formats
OLE DB Data Type Default SAS Format

DBTYPE_R8 none

DBTYPE_R4 none

DBTYPE_I8 none

DBTYPE_UI8 none

DBTYPE_I4 11.

DBTYPE_UI4 11.

DBTYPE_I2 6.

DBTYPE_UI2 6.

DBTYPE_I1 4.

DBTYPE_UI1 4.

DBTYPE_BOOL 1.

DBTYPE_NUMERIC m or m.n or none, if m and n are not specified

DBTYPE_DECIMAL m or m.n or none, if m and n are not specified

DBTYPE_CY DOLLARm.2

DBTYPE_BYTES $n.

DBTYPE_STR $n.

DBTYPE_BSTR $n.

DBTYPE_WSTR $n.

DBTYPE_DBDATE DATE9.

DBTYPE_DBTIME TIME8.

DBTYPE_TIMESTAMP and DBTYPE_DATE DATETIMEm.n, where m depends on precision and ndepends on scale

***OLE DB Data Types and Default SAS Formats***
OLE DB Data Type	Default SAS Format
DBTYPE_R8	none
DBTYPE_R4	none
DBTYPE_I8	none
DBTYPE_UI8	none
DBTYPE_I4	11.
DBTYPE_UI4	11.
DBTYPE_I2	6.
DBTYPE_UI2	6.
DBTYPE_I1	4.
DBTYPE_UI1	4.
DBTYPE_BOOL	1.
DBTYPE_NUMERIC	m or m.n or none, if m and n are not specified
DBTYPE_DECIMAL	m or m.n or none, if m and n are not specified
DBTYPE_CY	DOLLARm.2
DBTYPE_BYTES	$n.
DBTYPE_STR	$n.
DBTYPE_BSTR	$n.
DBTYPE_WSTR	$n.
DBTYPE_DBDATE	DATE9.
DBTYPE_DBTIME	TIME8.
DBTYPE_TIMESTAMP and DBTYPE_DATE	DATETIMEm.n, where m depends on precision and ndepends on scale

Default OLE DB Output Data Types shows the default data types that the SAS/ACCESS engine for OLE DB uses when creating DBMS tables.

Default OLE DB Output Data Types
SAS Variable Format Default OLE DB Data Type

m.n DBTYPE_R8 or DBTYPE_NUMERIC using m.n if the DBMS allows it

$n. DBTYPE_STR using n

date formats DBTYPE_DBDATE

time formats DBTYPE_DBIME

datetime formats DBTYPE_DBTIMESTAMP

***Default OLE DB Output Data Types***
SAS Variable Format	Default OLE DB Data Type
m.n	DBTYPE_R8 or DBTYPE_NUMERIC using m.n if the DBMS allows it
$n.	DBTYPE_STR using n
date formats	DBTYPE_DBDATE
time formats	DBTYPE_DBIME
datetime formats	DBTYPE_DBTIMESTAMP

The SAS/ACCESS engine for OLE DB allows non-default data types to be specified with the DBTYPE= data set option. See DBTYPE= for more information about this data set option.

Chapter Contents
Previous
Next
Top of Page