Specifies data types to override the default SAS
data types during input processing.
Valid in:
DATA and PROC steps (when accessing DBMS data using SAS/ACCESS
software)
Default:
DBMS-specific
Data source:
Aster nCluster,
DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HP Neoview,
Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle,
Sybase IQ, Teradata
specifies a SAS data
type, which can be CHAR(n),
NUMERIC, DATETIME, DATE, TIME. See the DBMS-specific reference section
for your SAS/ACCESS interface for details.
Details
By default, the SAS/ACCESS
interface for your DBMS converts each DBMS data type to a SAS data
type during input processing. When you need a different data type,
you can use this option to override the default and assign a SAS data
type to each specified DBMS column. Some conversions might not be
supported. In that case, SAS prints an error to the log.
Examples
Example 1: Override the Default Data Type
In this example, DBSASTYPE=
specifies a data type to use for the MYCOLUMN column when SAS prints
ODBC data. SAS can print the values if the data in this DBMS column
is stored in a format that SAS does not support, such as SQL_DOUBLE(20).
In the next example,
data that is stored in the DBMS FIBERSIZE column has a data type that
provides more precision than what SAS could accurately support, such
as DECIMAL(20). If you use only PROC PRINT on the DBMS table, the
data might be rounded or are displayed as a missing value. So you
could use DBSASTYPE= instead to convert the column so that the length
of the character field is 21. The DBMS performs the conversion before
the data is brought into SAS, so precision is preserved.
The next example uses
DBSASTYPE= to append one table to another when the data types cannot
be compared. If the EMPID variable in the SAS data set is defined
as CHAR(20) and the EMPID column in the DBMS table is defined as DECIMAL(20),
you can use DBSASTYPE= to make them match:
DBSASTYPE= specifies
to SAS that the EMPID is defined as a character field of length 20.
When a row is inserted from the SAS data set into a DBMS table, the
DBMS performs a conversion of the character field to the DBMS data
type DECIMAL(20).