DBSASTYPE=

DBSASTYPE=

Specifies data type(s) to override the default SAS data type(s) during input processing of data from ORACLE.

Default value: Option is omitted

Syntax
Details

Syntax

DBSASTYPE=(<column-name-1=<'>SAS-data-type<'>> <...<column-name-n=<'SAS-data-type<'>>>)

column-name: specifies a DBMS column name.
SAS-data-type: specifies a SAS data type, which can be one of the following: CHAR(length), NUMERIC, DATETIME, DATE, or TIME.

Details

This option is valid only when you read ORACLE data into SAS.

By default, the SAS/ACCESS Interface to ORACLE converts each ORACLE data type to a predetermined SAS data type when processing data from ORACLE. When you need a different data type, you can use DBSASTYPE= to override the default data type chosen by the SAS/ACCESS engine.

In the following example, the data stored in the DBMS FIBERSIZE column has a data type that provides more precision than what SAS could accurately support, such as DECIMAL(20). If you just used a PROC PRINT on the DBMS table, the data might be rounded or displayed as a missing value. Instead, you could use the DBSASTYPE= option to convert the column to a character field of the length 21. Because the ORACLE performs the conversion before the data is brought into SAS, there is no loss of precision.

proc print data=mylib.specprod
   (DBSASTYPE=(fibersize='CHAR(21)'));
run;

You can also use the DBSASTYPE= option in cases where you are appending one DBMS table to another table, and the data types are not comparable. If the SAS data set has a variable CITY defined as CHAR(20) and the table has a column defined as DECIMAL (20), you can use DBSASTYPE= to make them match:

proc append base=dblib.hrdata (DBSASTYPE=(city='CHAR(20)'));
            data=saslib.personnel;
run;

DBSASTYPE= specifies to SAS that the CITY is defined as a character field of length 20. When a row is inserted from the SAS data set into a DBMS table, ORACLE performs a conversion of the character field to the DBMS data type, DECIMAL(20).

See LIBNAME Statement Data Conversions for more details about the default data types for ORACLE.

Chapter Contents
Previous
Next
Top of Page