SAS/ACCESS Software for Relational Databases: Reference

ODBC Data Types

Every column in a table has a name and a data type. The data type tells the DBMS how much physical storage to set aside for the column and the form in which the data are stored.

ODBC Data Types and Default SAS Formats shows all of the data types and default SAS formats that are supported by the ODBC engine. This table does not explicitly define the data types as they exist for each DBMS. It lists the SQL types that each DBMS data type would map to. For example, a CHAR data type under DB2 would map to an ODBC data type of SQL_CHAR. There are no unsupported data types.

ODBC Data Types and Default SAS Formats
ODBC Data Type Default SAS Format

SQL_CHAR $n

SQL_VARCHAR $n

SQL_LONGVARCHAR $n

SQL_BINARY $n.*

SQL_VARBINARY $n.*

SQL_LONGVARBINARY $n.*

SQL_DECIMAL m or m.n or none if m and n are not specified

SQL_NUMERIC m or m.n or none if m and n are not specified

SQL_INTEGER 11.

SQL_SMALLINT 6.

SQL_TINYINT 4.

SQL_BIT 1.

SQL_REAL none

SQL_FLOAT none

SQL_DOUBLE none

SQL_BIGINT none

SQL_DATE DATE9.

SQL_TIME TIME8.
ODBC cannot support fractions of seconds for time values

SQL_TIMESTAMP DATETIMEm.n where m and n depend on precision

* Because the ODBC driver does the conversion, this field will be displayed as though the $HEXn. format were applied.

***ODBC Data Types and Default SAS Formats***
ODBC Data Type	Default SAS Format
SQL_CHAR	$n
SQL_VARCHAR	$n
SQL_LONGVARCHAR	$n
SQL_BINARY	$n.*
SQL_VARBINARY	$n.*
SQL_LONGVARBINARY	$n.*
SQL_DECIMAL	m or m.n or none if m and n are not specified
SQL_NUMERIC	m or m.n or none if m and n are not specified
SQL_INTEGER	11.
SQL_SMALLINT	6.
SQL_TINYINT	4.
SQL_BIT	1.
SQL_REAL	none
SQL_FLOAT	none
SQL_DOUBLE	none
SQL_BIGINT	none
SQL_DATE	DATE9.
SQL_TIME	TIME8. ODBC cannot support fractions of seconds for time values
SQL_TIMESTAMP	DATETIMEm.n where m and n depend on precision
* Because the ODBC driver does the conversion, this field will be displayed as though the $HEXn. format were applied.

Default ODBC Output Data Types shows the default data types that the ODBC engine uses when creating tables.

Default ODBC Output Data Types
SAS Variable Format Default ODBC Data Type

m.n SQL_DOUBLE or SQL_NUMERIC using m.n if the DBMS allows it

$n. SQL_VARCHAR using n

datetime formats SQL_TIMESTAMP

date formats SQL_DATE

time formats SQL_TIME

***Default ODBC Output Data Types***
SAS Variable Format	Default ODBC Data Type
m.n	SQL_DOUBLE or SQL_NUMERIC using m.n if the DBMS allows it
$n.	SQL_VARCHAR using n
datetime formats	SQL_TIMESTAMP
date formats	SQL_DATE
time formats	SQL_TIME

The ODBC engine allows nondefault data types to be specified with the DBTYPE= data set option. See DBTYPE= for more information on this data set option.

Chapter Contents
Previous
Next
Top of Page