dbType= Data Connector Option

specifies alternative data types for specified columns.

Valid in:	CASLIB statement
Valid in:	PROC CASUTIL: SAVE statement
Default:	none
Restriction:	You must specify a valid column name for this option. If you specify a column name that is not part of the table that you are writing to your data source, an error is written in the log.
Interaction:	Microsoft SQL Server: Use the connectionTypeForCreate= data connector option to determine the list of valid data types to specify for your data source.
Data source:	DuckDB, Greenplum, Impala, JDBC, Microsoft SQL Server, MongoDB, MySQL, Oracle, PostgreSQL, Salesforce, SAP HANA, SingleStore standard, Snowflake, Yellowbrick
Note:	Support for DuckDB was added in 2026.09.
See:	connectionTypeForCreate= data connector option

Table of Contents

Syntax

dbType="column1=<'>data-type1<'> <column2=<'>data-type2<'>...>"

Required Argument

column=data-type

specifies one or more column names and corresponding data types that should be used when writing to a table in the data source. Separate multiple column and data type pairs with a space.

Details

Use This Option with Caution

Use caution when you use the dbType= data connector option. Using alternative data types instead of default data type conversions can result in loss of numeric precision or data conversion errors. Check your log carefully to ensure that all data was written successfully to your external data source.

Be careful if you use the dbType= option to specify an alternative numeric value when you save data to an external table. If a numeric variable is normally saved to a BIGINT data type, but you save it to an INTEGER type, precision for very large values is at risk. For more information, see the information about "Integer Data Types and Numeric Precision" for your data connector.

In addition, by selecting a data type conversion that differs from the default data conversion, it is possible that some values lie outside of the acceptable range for the data type that you select. For example, suppose that you have a variable that includes a 2000-character string, and you want to save that variable to a SMALLINT data type. Writing the rows to an external table is likely to fail and generate a data conversion error.

Further, how a data conversion failure is handled varies based on your data source. For some data sources, if an invalid value is encountered, all updated rows are removed from the external table (a rollback). Other data sources insert rows until an invalid value is encountered and stop updating the external table at that point. Neither case is desirable, since either no data or a subset of your data is written to the external table.

Note: If your data source does not roll back updates due to errors, the subset of data that is written to your external table can vary each time you attempt to write data with conversion errors to your data source. This is because writing to external tables typically uses multiple CAS nodes. This means that the number of rows written and the order of rows can differ each time.

Overview of Available Data Types

The available data types for your data source are based on those that are used for PROC FEDSQL. Use the links below to determine the data types that are available to your data source.

Links to Available Data Types
Data Source	FedSQL Data Types
Greenplum	Data Types for Greenplum
Impala	Data Types for Impala
JDBC	Data Types for JDBC
Microsoft SQL Server	Data Types for Microsoft SQL Server See also: Additional Data Type Support for Microsoft SQL Server
MongoDB	Supports:1 BIGINT CHAR(n) DOUBLE INTEGER TIMESTAMP VARCHAR
MySQL	Data Types for MySQL
Oracle	Data Types for Oracle
PostgreSQL	Data Types for PostgreSQL
Salesforce	Data Types for Writing to Salesforce
SAP HANA	Data Types for SAP HANA
SingleStore standard	Data Types for SingleStore
Snowflake	Data Types for Snowflake
Yellowbrick	Data Types for Yellowbrick
1 The list of supported data types for dbType= is a subset of all FedSQL data types.

Additional Data Type Support for Microsoft SQL Server

There are additional data types that are supported for Microsoft SQL Server, including support for large character values. Support for Microsoft SQL Server includes the related connectionTypeForCreate= data connector option. When you set connectionTypeForCreate="native", you can specify any data type that is supported for Microsoft SQL Server. However, it is recommended that you specify data types that can be read back into SAS so that you can load data for additional calculations. For more information, see connectionTypeForCreate= Data Connector Option.

Example

In this example, you have a table called Cars_toyota in caslib Mycaslib. You want to save the table back out to your PostgreSQL data source, but you want to save the MPG_City and MPG_Highway columns as SMALLINT values instead of DOUBLES. Use the dbType= option to change the default data type for these columns when you save the file to PostgreSQL. Use the output from the CONTENTS statement to verify that the data types were assigned correctly.

proc casutil incaslib=mycaslib;
  save casdata='cars_toyota' outcasib='mycaslib' 
      casout='cars_subset' 
      options=(dbtype="MPG_City='SMALLINT' MPG_Highway='SMALLINT'");
  contents casdata='cars_subset';
quit;

Last updated: September 15, 2026