Supported Data Types

SAS Data Types

Internally, SAS supports two data types for storing data:
CHAR
fixed-length character data, 32,767-character maximum
NUM
double-precision floating-point number
Note: If the data field is longer than 254 characters, the drivers process it as the ODBC data type SQL_VARCHAR.
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.

ODBC SQL Data Types

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.
SAS Drivers for ODBC and Corresponding Data Types
SQL Data Type
SAS Drivers for ODBC Implementation
SQL_CHAR
Supports text fields up to 200 characters long
SQL_VARCHAR
Interpreted for text columns of length 80 or greater when the Support VARCHAR option is selected in the SQL Options section of the SAS ODBC Driver Configuration dialog box; the maximum length of an SQL_VARCHAR field is 32,767 characters, and is available when the version of the SAS server is Version 7 or later; for Version 6 SAS servers, the SQL_VARCHAR field is limited to 200 characters
SQL_DOUBLE
1.E-308 to 1.E308
SQL_FLOAT
1.E-308 to 1.E308
SQL_INTEGER
-2,147,483,648 to 2,147,483,647
SQL_DATE
Valid dates range from 1582 A.D. to 9999 A.D.
SQL_TIME
ODBC supports time values within the range of a 24-hour day (00:00:00 to 23:59:59)
SQL_TIMESTAMP
Valid dates range from 1582 A.D. to 9999 A.D. with a 24-hour day (00:00:00 to 23:59:59) time portion

Data Types Reported on Queries

When the SQLDescribeCol and SQLColAttributes functions are called against active queries, the drivers report data types as follows:
  • When the SQLDescribeCol function is called, the drivers report CHAR data types as SQL_CHAR. NUM data types are generally reported as SQL_DOUBLE.
    However, SAS stores dates and times as numbers, and the drivers use SAS format information to infer the following SQL data types from NUM data types:(footnote1)
    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 drivers identify the column as financial data (having a column attribute of SQL_COLUMN_MONEY).

Retrieving Native SAS Format Information

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.

Using Data Types in CREATE TABLE Statements

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:
CREATE TABLE Data Types and SAS Data Types
CREATE TABLE
Data Type Name
ODBC Data Type
SAS Data Type
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=DATE9X.
datetime19x
SQL_TIMESTAMP
NUM FORMAT=DATETIME19X.
time8x
SQL_TIME
NUM FORMAT=TIME8X.
timestamp
SQL_TIMESTAMP
NUM FORMAT=DATETIME26.6
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
);

Comparing Date, Time, and Datetime Values

When you 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 a.m., and 86399 is 11:59:59 p.m.
    Note: ODBC does not support negative time values or values greater than one day's worth of seconds. The drivers return 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 drivers.
CAUTION:
You can compare equivalent literals against SAS date, time, or datetime values only because 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 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
FOOTNOTE 1:For a complete list of date and time formats that the drivers support, see the table of formats listed by categories in SAS Formats and Informats: Reference.[return]