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
Default:	DBMS-specific
Supports:	All

Syntax

Details

Examples

Example 1: Specify Data Types for Columns

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

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

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 data source for the default data types for your DBMS.

Details

By default, the interface for your DBMS converts each SAS data type to a predetermined DBMS data type when writing data to your DBMS. When you need a different data type, use DBTYPE= to override the default data type.

Teradata Details: 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

Example 1: Specify Data Types for Columns

In the following example, DBTYPE= specifies the data types that are used 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

The following 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

The following 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 those that are required by the Teradata FORMAT attribute. 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;