SAS/ACCESS Software for Relational Databases: Reference

ORACLE7 Server Data Types

Every column in a table has a name and a data type. The data type tells ORACLE how much physical storage to set aside for the column and the form in which the data is stored. ORACLE data types fall into three categories: types for character data, types for numeric data, and types for abstract values such as dates. Each of these types is described in the following sections.

Note: The SAS/ACCESS Interface to ORACLE does not support the following ORACLE data types: MLSLABEL and ROWID. SAS/ACCESS provides an error message when it attempts to read a table that has at least one column that uses an unsupported data type. [cautionend]

Character Data

CHAR (n): contains fixed-length character string data with a length of n, where n must be at least 1 and cannot exceed 255 characters. (The limit is 2,000 characters with ORACLE8 Server.) Note that the ORACLE7 Server CHAR data type is not equivalent to the ORACLE Version 6 CHAR data type. The ORACLE7 Server CHAR data type is new with ORACLE7 Server and uses blank-padded comparison semantics.
LONG: contains varying-length character string data that is similar to type VARCHAR2. Type LONG is character data of variable length with a maximum length of 2 gigabytes. You can define only one LONG column per table. Available memory considerations might also limit the size of a LONG data type.
VARCHAR2(n): contains character string data with a length of n, where n must be at least 1 and cannot exceed 2000 characters. (The limit is 4,000 characters with ORACLE8 Server.) The VARCHAR2 data type is equivalent to the ORACLE Version 6 CHAR data type except for the difference in maximum lengths. The VARCHAR2 data type uses nonpadded comparison semantics.

Numeric Data

NUMBER(p,s): specifies a fixed-point number with an implicit decimal point, where p is the total number of digits (precision) and can range from 1 to 38, and s is the number of digits to the right of the decimal point (scale) and can range from -84 to 127.
NUMBER(p): specifies an integer of precision p that can range from 1 to 38 and a scale of 0.
NUMBER: specifies a floating-point number with a precision of 38. A floating-point value can either specify a decimal point anywhere from the first to the last digit or omit the decimal point. A scale value does not apply to floating-point numbers since there is no restriction on the number of digits that can appear after the decimal point.

Other Data Types

DATE: contains date values. Valid dates are from January 1, 4712 BC to December 31, 4712 AD. The default format is DD-MON-YY, for example '05-OCT-98'.
LONG RAW: contains raw binary data of variable length up to 2 gigabytes. Values entered into columns of this type must be inserted as character strings in hexadecimal notation.
RAW(n): contains raw binary data where n must be at least 1 and cannot exceed 255 bytes. (In ORACLE Version 8, the limit is 2,000 bytes.) Values entered into columns of this type must be inserted as character strings in hexadecimal notation. You must specify n for this data type.

Note: For compatibility with other DBMSs, ORACLE supports the syntax for a wide variety of numeric data types, including DECIMAL, INTEGER, REAL, DOUBLE-PRECISION, and SMALLINT. All forms of numeric data types are actually stored in the same internal ORACLE NUMBER format. The additional numeric data types are variations of precision and scale. A null scale implies a floating-point number, and a non-null scale implies a fixed-point number. [cautionend]

See LIBNAME Statement Data Conversions and ACCESS Procedure Data Conversions for a description of how PROC ACCESS and the LIBNAME statement treat each of these types during input operations.

NULL and Default Values

ORACLE has a special value called NULL. NULL means an absence of information and is analogous to the SAS System's missing value. By default, columns accept NULL values. However, you can define columns so that they cannot contain NULL data. For example, the CREATE TABLE statement for the CUSTOMERS table in Appendix 1 defines the first column, CUSTOMER, as CHAR(8) and NOT NULL. NOT NULL tells ORACLE not to add a row to the table unless the row has a value for CUSTOMER.

LIBNAME Statement Data Conversions

LIBNAME Statement: Default SAS Formats for ORACLE Data Types shows the default SAS System variable formats that the LIBNAME statement assigns to ORACLE data types during input operations. You can override these input and output data types by using the DBTYPE= data set option during output processing.

Note: ORACLE data types that are omitted from this table are not supported by SAS/ACCESS. [cautionend]

LIBNAME Statement: Default SAS Formats for ORACLE Data Types
ORACLE Data Type Default SAS Format

CHAR(n) $n.

VARCHAR2(n) $n.

NUMBER none (BEST. on OS/390 and CMS)

NUMBER(p) w.(BEST. on OS/390 and CMS)

NUMBER(p, s) w.d

DATE DATETIME20.

LONG $200.

RAW(n) $HEXw.

LONG RAW $HEX200.

If ORACLE data falls outside valid SAS data ranges, the values are usually counted as missing.

***LIBNAME Statement: Default SAS Formats for ORACLE Data Types***
ORACLE Data Type	Default SAS Format
CHAR(n)	$n.
VARCHAR2(n)	$n.
NUMBER	none (BEST. on OS/390 and CMS)
NUMBER(p)	w.(BEST. on OS/390 and CMS)
NUMBER(p, s)	w.d
DATE	DATETIME20.
LONG	$200.
RAW(n)	$HEXw.
LONG RAW	$HEX200.

Note: SAS automatically converts ORACLE NUMBER types to SAS number formats by using an algorithm that determines the correct scale and precision. When the scale and precision cannot be determined, the SAS/ACCESS Interface to ORACLE allows the procedure or application to determine the format. [cautionend]

LIBNAME Statement: Default ORACLE Data Types for SAS Formats shows the default ORACLE data types that the LIBNAME statement assigns to SAS variable formats during output operations.

LIBNAME Statement: Default ORACLE Data Types for SAS Formats
SAS Variable Format ORACLE Data Type

$w.
VARCHAR2(n)

w. with SAS format name of NULL NUMBER(p)

w.d with SAS format name of NULL NUMBER(p,s)

all other numerics * NUMBER (NUMBER(38,10) on OS/390 and CMS)

datetimew.d
DATE

datew.
DATE

time. ** DATE

* Includes all SAS numeric formats, such as BINARY8 and E10.0.

** Includes all SAS time formats, such as TODw.d and HHMMw.d .

***LIBNAME Statement: Default ORACLE Data Types for SAS Formats***
SAS Variable Format	ORACLE Data Type
$w.	VARCHAR2(n)
w. with SAS format name of NULL	NUMBER(p)
w.d with SAS format name of NULL	NUMBER(p,s)
all other numerics *	NUMBER (NUMBER(38,10) on OS/390 and CMS)
datetimew.d	DATE
datew.	DATE
time. **	DATE

ACCESS Procedure Data Conversions

PROC ACCESS: Default SAS Formats for ORACLE Data Types shows the default SAS System variable formats that the ACCESS procedure assigns to ORACLE data types.

Note: ORACLE data types that are omitted from this table are not supported by the SAS/ACCESS Interface. [cautionend]

PROC ACCESS: Default SAS Formats for ORACLE Data Types
ORACLE Data Type Default SAS Format

CHAR(n) $n. (n <= 200) $200. (n > 200)

VARCHAR2(n) $n. (n <= 200) $200. (n > 200)

FLOAT BEST22.

NUMBER BEST22.

NUMBER(p) w.

NUMBER(p, s) w.d

DATE DATETIME16.

LONG $200.

RAW(n) $n. (n < 200) $200. (n > 200)

LONG RAW $200.

See "ACCESS Procedure Data Conversions for the NUMBER Data Type" for more information about NUMBER data type conversions. If ORACLE data fall outside valid SAS data ranges, the values are usually counted as missing.

***PROC ACCESS: Default SAS Formats for ORACLE Data Types***
ORACLE Data Type	Default SAS Format
CHAR(n)	$n. (n <= 200) $200. (n > 200)
VARCHAR2(n)	$n. (n <= 200) $200. (n > 200)
FLOAT	BEST22.
NUMBER	BEST22.
NUMBER(p)	w.
NUMBER(p, s)	w.d
DATE	DATETIME16.
LONG	$200.
RAW(n)	$n. (n < 200) $200. (n > 200)
LONG RAW	$200.

ACCESS Procedure Data Conversions for the NUMBER Data Type

The general form of an ORACLE number is NUMBER(p,s) where p is the precision and s is the scale of the number. ORACLE defines precision as the total number of digits, with a valid range of -84 to 127. However, a negative scale means that the number is rounded to the specified number of places to the left of the decimal. For example, if the number 1,234.56 is specified as data type NUMBER(8,-2), it is rounded to the nearest hundred and stored as 1,200.

Default SAS Formats for ORACLE NUMBER Data Types shows the correlation between the ORACLE NUMBER data types and the default SAS formats that are created from that data type.

Default SAS Formats for ORACLE NUMBER Data Types
ORACLE NUMBER Data Type Rules Default SAS Format

NUMBER(p) 0 < p <= 32 (p + 1).0

NUMBER(p,s) p > 0, s < 0, |s| < p
(p + |s| + 1).0

NUMBER(p,s) p > 0, s < 0, |s| >= p
(p + |s| + 1).0

NUMBER(p,s) p > 0, s > 0, s < p
(p + 2).s

NUMBER(p,s) p > 0, s > 0, s >= p
(s + 3).s

NUMBER(p) p > 32 BEST22. SAS selects format

NUMBER p,s unspecified BEST22. SAS selects format

***Default SAS Formats for ORACLE NUMBER Data Types***
ORACLE NUMBER Data Type	Rules	Default SAS Format
NUMBER(p)	0 < p <= 32	(p + 1).0
NUMBER(p,s)	p > 0, s < 0, \|s\| < p	(p + \|s\| + 1).0
NUMBER(p,s)	p > 0, s < 0, \|s\| >= p	(p + \|s\| + 1).0
NUMBER(p,s)	p > 0, s > 0, s < p	(p + 2).s
NUMBER(p,s)	p > 0, s > 0, s >= p	(s + 3).s
NUMBER(p)	p > 32	BEST22. SAS selects format
NUMBER	p,s unspecified	BEST22. SAS selects format

DBLOAD Procedure Data Conversions

PROC DBLOAD: Default ORACLE Data Types for SAS Formats shows the default ORACLE data types the DBLOAD procedure assigns to SAS variable formats.

PROC DBLOAD: Default ORACLE Data Types for SAS Formats
SAS Variable Format ORACLE Data Type

$w.
CHAR(n)

w. NUMBER(p)

w.d
NUMBER(p,s)

all other numerics * NUMBER

datetimew.d
DATE

datew.
DATE

time. ** NUMBER

* Includes all SAS numeric formats, such as BINARY8 and E10.0.

** Includes all SAS time formats, such as TODw.d and HHMMw.d .

***PROC DBLOAD: Default ORACLE Data Types for SAS Formats***
SAS Variable Format	ORACLE Data Type

$w.	CHAR(n)
w.	NUMBER(p)
w.d	NUMBER(p,s)
all other numerics *	NUMBER
datetimew.d	DATE
datew.	DATE
time. **	NUMBER

Chapter Contents
Previous
Next
Top of Page

*	Includes all SAS numeric formats, such as BINARY8 and E10.0.
**	Includes all SAS time formats, such as TODw.d and HHMMw.d .