Data Set Options for Relational Databases

DBTYPE= Data Set Option

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

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, Sybase IQ, Teradata

Syntax
Syntax Description
Details
Examples
See Also

Syntax

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

Syntax Description

column-name: specifies a DBMS column name.
DBMS-type: specifies a DBMS data type. See the documentation 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 outputs 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.

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: In 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.

Examples

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;

This next 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;

The next 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;

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))