In CREATE TABLE requests, the
SAS ODBC Driver interprets 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
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 SAS ODBC Driver
translates 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 SAS ODBC Driver passes 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 (
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'},
{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.000000000'dt,
'01jan1960'd,
'00:00:00't
);