DBTYPE= Data Set Option

Specifies a data type to use instead of the default DBMS data type when SAS creates a DBMS table.

Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
Category:	Data Set Control
Default:	DBMS-specific
Restriction:	You cannot use this option with the HDFS_METADIR= connection option.
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 ASE, SAP IQ, Snowflake, Teradata, Vertica, Yellowbrick
Note:	Support for Yellowbrick was added in SAS 9.4M7.
See:	DBCREATE_TABLE_OPTS= data set option, DBFORCE= data set option, DBNULL= data set option

Table of Contents

Syntax

DBTYPE=(column-name-1=<'> DMBS-type<'>
<…column-name-n=<'> DBMS-type<'> > )

Syntax Description

column-name

specifies a DBMS column name.

DBMS-type

specifies a DBMS data type. See the DBMS-specific reference section for your SAS/ACCESS interface for the default data types for your DBMS.

Details

By default, the SAS/ACCESS interface for your DBMS converts each SAS data type to a predetermined DBMS data type when it writes data to your DBMS. When you need a different data type, use DBTYPE= to override the default data type chosen by the SAS/ACCESS engine.

You can also use this option to specify column modifiers. The allowable syntax for these modifiers is generally DBMS-specific. For more information, see the SQL reference for your database.

Google BigQuery: The following table shows the conversion from the data type that you specify for DBTYPE= to the resulting Google BigQuery data type when you create a table in the DBMS.

Conversion from DBTYPE= to Google BigQuery Data Types
Data Type Specified for DBTYPE=	Resulting Google BigQuery Data Type
BIGINT	INT64
DATE	DATE
DOUBLE	FLOAT64
TIME	TIME
TIMESTAMP	TIMESTAMP
VARBINARY	BYTES
VARCHAR	STRING

MySQL: All text strings are passed as is to the MySQL server. MySQL truncates text strings to fit the maximum length of the field without generating an error message.

Teradata: You can use DBTYPE= to specify data attributes for a column. See your Teradata CREATE TABLE documentation for information about the data type attributes that you can specify. If you specify DBNULL=NO for a column, do not also use DBTYPE= to specify NOT NULL for that column. If you do, NOT NULL is inserted twice in the column definition. This causes Teradata to generate an error message.

Vertica: The default is none.

Examples

Example 1: Specify Data Types for Columns

In this example, DBTYPE= specifies the data types to use when you create columns in the DBMS table.

data mydblib.newdept(dbtype=(deptno='number(10,2)' city='char(25)'));
   set mydblib.dept;
run;

Example 2: Specify Data Types for Columns in a New Table

This example creates a new Teradata table, Newdept, specifying the Teradata data types for the DEPTNO and CITY columns.

data mydblib.newdept(dbtype=(deptno='byteint' city='char(25)'));
set dept;
run;

Example 3: Specify a Data Type for a Column in a New Table

This example creates a new Teradata table, Newemployees, and specifies a data type and attributes for the EMPNO column. The example encloses the Teradata type and attribute information in double quotation marks. Single quotation marks conflict with single quotation marks that the Teradata FORMAT attribute requires. If you use single quotation marks, SAS returns syntax error messages.

data mydblib.newemployees(dbtype= (empno="SMALLINT FORMAT '9(5)'
     CHECK (empno >= 100 AND empno <= 2000)"));
set mydblib.employees;
run;

Example 4: Create a Primary Key for a New Table

Where x indicates the Oracle engine, this example creates a new table, Allacctx, and uses DBTYPE= to create the primary key, Allacct_Pk.

data x.ALLACCTX  ( dbtype=(
SourceSystem  = 'varchar(4)'
acctnum  = 'numeric(18,5) CONSTRAINT "ALLACCT_PK" PRIMARY KEY'
accttype  = 'numeric(18,5)'
balance  = 'numeric(18,5)'
clientid  = 'numeric(18,5)'
closedate  = 'date'
opendate  = 'date'
primary_cd  = 'numeric(18,5)'
status  = 'varchar(1)'
)  );
set work.ALLACCT ;
format CLOSEDATE date9.;
format OPENDATE  date9.;
run;

The code generates this CREATE TABLE statement.

Output from a CREATE TABLE Statement That Uses DBTYPE= to Specify a Column Modifier

CREATE TABLE ALLACCTX(SourceSystem varchar(4),
cctnum numeric(18,5) CONSTRAINT "ALLACCT_PK" PRIMARY KEY,
ccttype numeric(18,5),balance numeric(18,5),clientid numeric(18,5),
losedate date,opendate date,primary_cd numeric(18,5),status varchar(1))

Example 5: Data Type Conversions When Creating a Table in Google BigQuery

Given that x indicates a Google BigQuery libref, the following DATA step creates the table Mytest in the Google BigQuery database.

data x.mytest(dbtype=(icol1='bigint' charcol1='varchar(20)'));
   icol1=1; charcol1='test1';
run;

The resulting table, Mytest, is created where column Icol1 is of type INT64 and column Charcol1 is of type STRING. For more information, see the information about Google BigQuery in the Details section above.

Last updated: February 3, 2026