SAS/ACCESS Software for Relational Databases: Reference

Oracle Rdb Data Types

Every column in a table has a name and a data type. The data type tells Oracle Rdb how much physical storage to set aside for the column and the format in which the data is stored. The Oracle Rdb data types are described in the following tables

For more information on these data types, see your Oracle Rdb documentation.

String Data

String Data Types
Oracle Rdb SQL Description

CHAR(n) fixed-length column for character string data composed of 8-bit ASCII bytes where n specifies the length of the string. Use an unsigned integer (n). If you omit n , a 1-character column is created. Maximum n is 65,271.

VARCHAR(n) varying-length column for character string data composed of 8-bit ASCII bytes, where n specifies the maximum length of the string. Use an unsigned integer (n). Maximum n is 65,269 characters.

LONG VARCHAR varying-length column for character string data, which has 16,383 characters. LONG VARCHAR is equivalent to specifying VARCHAR(16,383).

***String Data Types***
Oracle Rdb SQL	Description
CHAR(n)	fixed-length column for character string data composed of 8-bit ASCII bytes where n specifies the length of the string. Use an unsigned integer (n). If you omit n , a 1-character column is created. Maximum n is 65,271.
VARCHAR(n)	varying-length column for character string data composed of 8-bit ASCII bytes, where n specifies the maximum length of the string. Use an unsigned integer (n). Maximum n is 65,269 characters.
LONG VARCHAR	varying-length column for character string data, which has 16,383 characters. LONG VARCHAR is equivalent to specifying VARCHAR(16,383).

Numeric Data

Numeric Data Types
Oracle Rdb SQL Description

TINYINT(n)^*
a tiny integer (signed byte, 8 contiguous bits). Can store a range of values from -128 through 127.

SMALLINT(n)^*
a small integer (signed 16-bit word). Can store a range of values from -32,768 to 32,767.

INTEGER(n)^*
an integer (signed 32-bit longword). Can store a range of values from -2**31 to (2**31)-1. You can abbreviate INTEGER to INT in your program.

BIGINT(n)^*
a signed 64-bit quadword. Can store a range of values from -2**63 to (2**63) -1.

FLOAT (n) a real (32-bit) or double precision (64-bit) floating-point number, depending on the precision indicated in the positive integer (n). If n is less than 25, FLOAT specifies a 32-bit floating-point number. If n is 25 or greater, FLOAT specifies a 64-bit floating-point number. The maximum value for n is 53. If n is omitted, FLOAT specifies a 64-bit floating-point number.

REAL a floating-point number (32-bit) with precision to 24 binary digits.

DOUBLE PRECISION a floating-point number (64-bit) with precision to 53 binary digits. (The way this data type is stored is the closest match to the way that the SAS System stores its numeric data type; therefore, numeric columns of this type require the least processing when being accessed by the SAS System.)

*(n) represents a scale factor that indicates the number of places to the right of the decimal point, and must be an integer in the range from 0 to 127.

***Numeric Data Types***
Oracle Rdb SQL	Description
TINYINT(n)^*	a tiny integer (signed byte, 8 contiguous bits). Can store a range of values from -128 through 127.
SMALLINT(n)^*	a small integer (signed 16-bit word). Can store a range of values from -32,768 to 32,767.
INTEGER(n)^*	an integer (signed 32-bit longword). Can store a range of values from -231 to (231)-1. You can abbreviate INTEGER to INT in your program.
BIGINT(n)^*	a signed 64-bit quadword. Can store a range of values from -263 to (263) -1.
FLOAT (n)	a real (32-bit) or double precision (64-bit) floating-point number, depending on the precision indicated in the positive integer (n). If n is less than 25, FLOAT specifies a 32-bit floating-point number. If n is 25 or greater, FLOAT specifies a 64-bit floating-point number. The maximum value for n is 53. If n is omitted, FLOAT specifies a 64-bit floating-point number.
REAL	a floating-point number (32-bit) with precision to 24 binary digits.
DOUBLE PRECISION	a floating-point number (64-bit) with precision to 53 binary digits. (The way this data type is stored is the closest match to the way that the SAS System stores its numeric data type; therefore, numeric columns of this type require the least processing when being accessed by the SAS System.)
*(n) represents a scale factor that indicates the number of places to the right of the decimal point, and must be an integer in the range from 0 to 127.

Date-Time Data

Date-time Data Types
Oracle Rdb SQL Description

DATE ANSI specifies a DATE that contains YEAR to DAY

DATE |DATE VMS specifies a timestamp that contains YEAR to SECOND

***Date-time Data Types***
Oracle Rdb SQL	Description
DATE ANSI	specifies a DATE that contains YEAR to DAY
DATE \|DATE VMS	specifies a timestamp that contains YEAR to SECOND

Oracle Rdb Nulls

Oracle Rdb has a special value that is called NULL, which signifies an absence of information and is analogous to the SAS System's missing value. By default, columns allow NULL values.

Columns can be defined so that they cannot contain NULL data. For example, the CREATE TABLE statement for the CUSTOMERS table defines the first column, CUSTOMER, as CHAR(8) and NOT NULL. NOT NULL tells Oracle Rdb not to add a row to the table unless the row has a value for CUSTOMER.

ACCESS Procedure Data Conversions

The following table shows the default SAS System variable formats that the ACCESS procedure assigns to each Oracle Rdb SQL data type. To calculate some of the SAS formats (for example, (5+n).n), see the explanation following the table.

Default SAS System Variable Formats for Oracle Rdb SQL Data Types--ACCESS Procedure
Oracle Rdb SQL Data Type SAS Variable Format^*

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

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

LONG VARCHAR $200.

TINYINT 4.0

TINYINT(n) (3+n).n

SMALLINT 6.0

SMALLINT(n) (5 + n).n

INTEGER 11.0

INTEGER(n) (10 + n).n

BIGINT 20.0

BIGINT(n) (19 + n).n

REAL E14.0

DOUBLE PRECISION E23.0

DATE DATETIME21.2

DECIMAL NUMERIC(n,n) Oracle Rdb SQL converts to other numeric type. SAS/ACCESS supports the converted type.

LIST OF BYTE VARYING unsupported

datetime intervals unsupported

*constant + n.n in Oracle Rdb SQL data types is equivalent to w.d in SAS formats.

***Default SAS System Variable Formats for Oracle Rdb SQL Data Types--ACCESS Procedure***
Oracle Rdb SQL Data Type	SAS Variable Format^*
CHAR(n)	$n.n < 200 $200. n >= 200
VARCHAR(n)	$n.n < 200 $200. n >= 200
LONG VARCHAR	$200.
TINYINT	4.0
TINYINT(n)	(3+n).n
SMALLINT	6.0
SMALLINT(n)	(5 + n).n
INTEGER	11.0
INTEGER(n)	(10 + n).n
BIGINT	20.0
BIGINT(n)	(19 + n).n
REAL	E14.0
DOUBLE PRECISION	E23.0
DATE	DATETIME21.2
DECIMAL NUMERIC(n,n)	Oracle Rdb SQL converts to other numeric type. SAS/ACCESS supports the converted type.
LIST OF BYTE VARYING	unsupported
datetime intervals	unsupported
*constant + n.n in Oracle Rdb SQL data types is equivalent to w.d in SAS formats.

To determine how an Oracle Rdb SQL data type is formatted by the SAS System, use the following conversion example, SMALLINT(n) to (5 + n).n :

SMALLINT(1) is equivalent to a SAS format of 6.1, or (5 + 1).1

SMALLINT(2) is equivalent to a SAS format of 7.2, or (5 + 2).2

SMALLINT(5) is equivalent to a SAS format of 10.5, or (5 + 5).5
Thus, the value of n is added to the 5 and the value of n also replaces the decimal value.

If Oracle Rdb data falls outside valid SAS data ranges, you get an error message in the SAS log when you try to read the data. For example, an Oracle Rdb SQL date might not fall in the valid SAS date range.

DBLOAD Procedure Data Conversions

The following table shows the default Oracle Rdb data types that the DBLOAD procedure assigns to each SAS variable format.

Default Oracle Rdb Data Types for SAS System Variable Formats--DBLOAD Procedure
SAS Variable
Format Oracle Rdb SQL Data Type

IBw.d, PIBw.d INTEGER

w.d
TINYINT(d)

w.d
SMALLINT(d)

w.d
INTEGER(d)

w.d
BIGINT(d)
QUADWORD(d)

w.d
DOUBLE PRECISION

$n. CHAR(n)

***Default Oracle Rdb Data Types for SAS System Variable Formats--DBLOAD Procedure***
SAS Variable Format	Oracle Rdb SQL Data Type
IBw.d, PIBw.d	INTEGER
w.d	TINYINT(d)
w.d	SMALLINT(d)
w.d	INTEGER(d)
w.d	BIGINT(d) QUADWORD(d)
w.d	DOUBLE PRECISION
$n.	CHAR(n)

Chapter Contents
Previous
Next
Top of Page

	SMALLINT(1) is equivalent to a SAS format of 6.1, or (5 + 1).1
	SMALLINT(2) is equivalent to a SAS format of 7.2, or (5 + 2).2
	SMALLINT(5) is equivalent to a SAS format of 10.5, or (5 + 5).5