Supported Data Types

Internally, SAS supports two data types for storing data:

By using SAS format information, the drivers are able to represent other ODBC data types, both when responding to queries, and in CREATE TABLE statements. (A SAS format is a string that describes how data should be printed. SAS associates format information with each column in a table.) For more information about SAS formats, see SAS Formats and Informats: Reference.

The following sections explain conventions for data type representation that the drivers follow.

The drivers support the ODBC SQL data types listed in the following table. Internally, SAS supports a numeric and character data type using informats and formats to modify the data representation. The drivers use the informat and format to determine the corresponding ODBC data type.

When the SQLDescribeCol and SQLColAttributes functions are called against active queries, the drivers report data types as follows:

You can find the SAS format for a column by using the SQLColumns function. The drivers return the additional column, FORMAT. The nineteenth column in the SQLColumns function result set contains a string with the format information for a SAS column of data.

In CREATE TABLE requests, the drivers interpret certain data-type specifications by creating NUM variables and associating SAS formats with them, as shown in the following table:

The data type names listed in the first column are the values that are returned by SQLColAttributes (with the parameter SQL_COLUMN_TYPE_NAME) and by SQLGetTypeInfo. For all CREATE TABLE statements, the drivers translate these data type names into the SAS data types shown in the third column. Do not try to use the ODBC data types directly in SAS.

As an alternative to using num(w, d) for creating SQL_FLOAT and SQL_DOUBLE columns, you can specify FORMAT= in a CREATE TABLE statement. In this case, the drivers pass the information to SAS unmodified. A column within a table (or data set) can be created based on 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 SAS:

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

The following code shows how to create a table with the time-related data types:

CREATE TABLE library.TIME_EXAMPLES (
  TIMESTAMP_COL TIMESTAMP,
  DATETIME_COL DATETIME19X,
  DATE_COL     DATE9X,
  TIME_COL     TIME8X
);

To insert date-related values, use SQL syntax similar to the following code:

INSERT INTO TABLE library.TIME_EXAMPLES (
  {ts'1960-01-01 00:00:00.000001'},
  {ts'1960-01-01 00:00:00},
  {d'1960-01-01'},
  {t'00:00:00'}
);

If you are more familiar with SAS date formats, then you can insert date-related values as shown in the following code:

INSERT INTO TABLE library.TIME_EXAMPLES (
  '01jan1960:00:00:00.000000'dt,
  '01jan1960:00:00:00'dt,
  '01jan1960'd,
  '00:00:00't
);

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

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

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

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

select * where hiredate = '02jan1995'd

Instead, use a datetime literal, such as either of the following:

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

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