SAS ODBC Driver User's Guide and Programmer's Reference

Supported Data Types

Internally, the SAS System supports two data types for storing data:

CHAR fixed-length character data, 200-character maximum

NUM double-precision floating-point number

However, by using SAS format information, the SAS ODBC driver is able to represent other ODBC data types, both when responding to queries and in CREATE TABLE requests. (A SAS format is a string that describes how data should be printed. The SAS System associates format information with each column in a table.)

The following sections explain conventions for data type representation that the SAS ODBC driver follows.

For information about user-specified SQL options that can also affect data type representations, see User-Specified SQL Options. For more information about SAS formats, see SAS Language Reference: Dictionary.

Data Types Reported on Queries

When the SQLDescribeCol and SQLColAttributes functions are called against active queries, the SAS ODBC driver reports data types as follows:

When the SQLDescribeCol function is called, the SAS ODBC driver reports CHAR data types as SQL_CHAR. NUM data types are generally reported as SQL_DOUBLE.
However, the SAS System stores dates and times as numbers, and the SAS ODBC driver uses SAS format information to infer the following additional SQL data types from NUM data types:(footnote 1)

SAS and SQL Data Types
SAS Data Type SQL Data Type

NUM FORMAT=DATEn. SQL_DATE

NUM FORMAT=TIMEn. SQL_TIME

NUM FORMAT=DATETIMEn. SQL_TIMESTAMP

In each of the previous FORMAT= strings, n is a number that selects the printable representation by specifying a width for printing. The value of n is not relevant to the driver.
When the SQLColAttributes function is called, if a NUM column has a format of DOLLARn., the SAS ODBC driver identifies it as financial data (having a column attribute of SQL_COLUMN_MONEY).

***SAS and SQL Data Types***
SAS Data Type	SQL Data Type
NUM FORMAT=DATEn.	SQL_DATE
NUM FORMAT=TIMEn.	SQL_TIME
NUM FORMAT=DATETIMEn.	SQL_TIMESTAMP

Creating or Comparing Date, Time, and Datetime Values

When you create or compare date, time and datetime values in SAS data sets from an ODBC application, you must consider the following:

A SAS time value is the number of seconds since the current day began. That is, 0 is 00:00:00 or 12:00:00 AM and 86399 is 11:59:59 PM.
Note: ODBC does not support negative time values or values greater than one day's worth of seconds. The SAS ODBC driver returns an error for time values that are less than 0 or greater than 86399 (the last second of the day).
A SAS date value is the number of days since January 1, 1960. That is, 0 is 01jan1960 and -1 is 31dec1959.
A SAS datetime value is the number of seconds since midnight on January 1, 1960. That is, 0 is 01jan1960:00:00:00 and -1 is 31dec1959:11:59:59.

Both ODBC and SAS date, time and datetime literals are supported by the SAS ODBC driver.

CAUTION:: You can only compare equivalent literals against SAS date, time or datetime values since they each have a different unit of measure.

For example, you cannot compare a SAS data set value that has been defined with a datetime format against a date literal using

   select * where hiredate = {d'1995-01-02'}

or
   select * where hiredate = '02jan1995'd

Instead, use a datetime literal such as

   select * where hiredate = {ts'1995-01-02 00:00:00'}

or
   select * where hiredate = '02jan1995:00:00:00'dt

Interpretation of Data Types in CREATE TABLE Requests

In CREATE TABLE requests, the SAS ODBC driver interprets certain column-type specifications by creating NUM variables and associating SAS formats with them, as shown in the following table:

Correspondence of CREATE TABLE Data Types and SAS Data Types
CREATE TABLE ODBC Data Type SAS Data Type

Data Type Name

char(w) SQL_CHAR CHAR(w)

num(w, d) SQL_DOUBLE NUM

num(w, d) SQL_FLOAT NUM

integer SQL_INTEGER NUM FORMAT=11.0

date9x SQL_DATE NUM FORMAT=DATE9.

datetime19x SQL_TIMESTAMP NUM FORMAT=DATETIME19.

time8x SQL_TIME NUM FORMAT=TIME8X

***Correspondence of CREATE TABLE Data Types and SAS Data Types***
CREATE TABLE	ODBC Data Type	SAS Data Type
Data Type Name
char(w)	SQL_CHAR	CHAR(w)
num(w, d)	SQL_DOUBLE	NUM
num(w, d)	SQL_FLOAT	NUM
integer	SQL_INTEGER	NUM FORMAT=11.0
date9x	SQL_DATE	NUM FORMAT=DATE9.
datetime19x	SQL_TIMESTAMP	NUM FORMAT=DATETIME19.
time8x	SQL_TIME	NUM FORMAT=TIME8X

The data type names listed in the first column of the table are the values that are returned by SQLColAttributes (with the parameter SQL_COLUMN_TYPE_NAME) and by SQLGetTypeInfo. For all CREATE TABLE statements, the SAS ODBC driver translates these data type names into the respective SAS data types shown under the SAS Data Type heading. Do not try to use the ODBC data types directly in SAS.

In a CREATE TABLE statement, any FORMAT= specification is passed on to the SAS System unmodified, so a column within a table (or data set) can be created according to any exact specification that is required for its use within SAS. For example, in the following CREATE TABLE statement, variable B's data type and format are passed directly to the SAS System.

CREATE TABLE
   SASUSER.TABLE1
      (A INTEGER,
       B NUM FORMAT=9.5,
       C CHAR(40) );

FOOTNOTE 1: For a complete list of date and time formats that the SAS ODBC driver supports, see the table of formats listed by categories in SAS Language Reference: Dictionary.

Chapter Contents
Previous
Next
Top of Page

CHAR	fixed-length character data, 200-character maximum
NUM	double-precision floating-point number