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) |
|---|---|
| Category: | Data Set Control |
| Default: | DBMS-specific |
| Restriction: | The Snowflake interface does not support UPDATE operations or using the DATA step MODIFY statement due to Snowflake client and database limitations. |
| Data source: | Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Google BigQuery, Greenplum, Hadoop, HAWQ, Impala, Informix, JDBC, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP HANA, SAP IQ, Snowflake, Teradata, Vertica, Yellowbrick |
| Note: | Support for Yellowbrick was added in SAS 9.4M7. |
Table of Contents
specifies a DBMS column name.
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.
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.
If you convert a long string value to the NUMERIC type, the numeric value that is stored in SAS might not exactly match the original character value. This happens with long strings that contain more than 15 significant digits. For example, if SAS reads in a character value of '123456789012345678901234567890' and converts that to type NUMERIC, then the numeric value that SAS stores is 12345678901234600000000000000. For more information, see Choosing Your Degree of Numeric Precision.
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).
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 SAS can accurately support, such as DECIMAL(20). If you use only PROC PRINT on the DBMS table, the data might be rounded or 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.
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 specified as CHAR(20) and the EMPID column in the DBMS table is specified 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 specified 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).