Data Set Options for Relational Databases |
Specifies
data types to override the default SAS data types during
input processing.
Default value: |
DBMS-specific
|
Valid in: |
DATA and PROC steps (when accessing DBMS data
using SAS/ACCESS software)
|
DBMS support: |
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
|
DBSASTYPE=(column-name-1=<'>SAS-data-type<'><...column-name-n=<'>SAS-data-type<'>>)
|
-
column-name
-
specifies a DBMS column name.
-
SAS-data-type
-
specifies a SAS data type, which can be
CHAR(n), NUMERIC, DATETIME, DATE, TIME. See your SAS/ACCESS interface
documentation for 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.
In this example, DBSASTYPE= specifies a data type to
use for the column MYCOLUMN when SAS is printing 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).
proc print data=mylib.mytable
(dbsastype=(mycolumn='CHAR(20)'));
run;
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 display 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.
proc print data=mylib.specprod
(dbsastype=(fibersize='CHAR(21)'));
run;
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:
proc append base=dblib.hrdata (dbsastype=(empid='CHAR(20)'))
data=saslib.personnel;
run;
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).
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.