Data Types for ODBC

Overview

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 is stored. This section includes information about ODBC null and default values and data conversions.

ODBC Null Values

Many relational database management systems have a special value called NULL. A DBMS NULL value means an absence of information and is analogous to a SAS missing value. When SAS/ACCESS reads a DBMS NULL value, it interprets it as a SAS missing value.
In most relational databases, columns can be defined as NOT NULL so that they require data (they cannot contain NULL values). When a column is defined as NOT NULL, the DBMS does not add a row to the table unless the row has a value for that column. When creating a DBMS table with SAS/ACCESS, you can use the DBNULL= Data Set Option data set option to indicate whether NULL is a valid value for specified columns.
ODBC mirrors the behavior of the underlying DBMS with regard to NULL values. See the documentation for your DBMS for information about how it handles NULL values.
For more information about how SAS handles NULL values, see Potential Result Set Differences When Processing Null Data .
To control how the DBMS handles SAS missing character values, use the NULLCHAR= and NULLCHARVAL= data set options.

LIBNAME Statement Data Conversions

This table shows all data types and default SAS formats that SAS/ACCESS Interface to ODBC supports. It 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. All data types are supported.
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.1
SQL_VARBINARY
$n.1
SQL_LONGVARBINARY
$n.1
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
20.
SQL_INTERVAL
$n
SQL_GUID
$n
SQL_TYPE_DATE
DATE9.
SQL_TYPE_TIME
TIME8.
ODBC cannot support fractions of seconds for time values
SQL_TYPE_TIMESTAMP
DATETIMEm.n where m and n depend on precision
1Because the ODBC driver does the conversion, this field is displayed as if the $HEXn. format were applied.
The following table shows the default data types that SAS/ACCESS Interface to ODBC uses when creating tables. SAS/ACCESS Interface to ODBC lets you specify non-default data types by using the DBTYPE= data set option.
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