SAS/ACCESS Software for Relational Databases: Reference

Informix Data Types and SAS Representations and Formats

Every column in a table has a name and a data type. The data type tells Informix how much physical storage to set aside for the column and the form in which the data is stored. Informix 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.

This section describes how the LIBNAME statement and the SQL Procedure Pass-Through Facility treat each of these types during input operations.

Character Data

CHAR(n), NCHAR(n): contains character string data from 1 to 32,767 characters in length and can include tabs and spaces.
VARCHAR(m, n), NVARCHAR(m, n): contains character string data from 1 to 255 characters in length.
TEXT: contains unlimited text data, depending on memory capacity.

Numeric Data

DECIMAL, MONEY, NUMERIC: contains numeric data with definable scale and precision. The amount of storage that is allocated depends on the size of the number.
FLOAT, DOUBLE PRECISION: contains double-precision numeric data up to 8 bytes.
INTEGER: contains an integer up to 32 bits (from -2³¹ to 2³¹--1).
REAL, SMALLFLOAT: contains single-precision, floating-point numbers up to 4 bytes.
SERIAL: stores sequential integers up to 32 bits.
SMALLINT: contains integers up to 2 bytes.

Abstract Data

DATE: contains a calendar date in the form of a signed integer value.
DATETIME: contains a calendar date and time of day stored in 2 to 11 bytes, depending on precision.
INTERVAL: contains a span of time stored in 2 to 12 bytes, depending on precision.

NULL and Default Values

If you do not indicate a default value for a column, the default value is NULL. You can specify the keywords NOT NULL after the data type of the column when you create a table to prevent NULL values from being stored in the column.

LIBNAME Statement Data Conversions

The following table shows the default SAS variable formats that the LIBNAME statement applies to Informix data types during input operations. You can override these default data types by using the DBTYPE= data set option on a specific data set.

LIBNAME Statement: Default SAS Formats for Informix Data Types
Informix Column Type Default SAS Format

CHAR(n) $n

DATE DATE9.

DATETIME** DATETIME24.5

DECIMAL none

DOUBLE PRECISION none

FLOAT none

INTEGER none

INTERVAL $n

MONEY none

NCHAR(n) $n
NLS support required

NUMERIC none

NVARCHAR(m,n)* $m
NLS support required

REAL none

SERIAL none

SMALLFLOAT none

SMALLINT none

TEXT* $n

VARCHAR(m,n)* $m

*Only supported by Informix-Online databases
**If the Informix field qualifier specifies either HOUR, MINUTE, SECOND, or FRACTION as the largest unit, the value is converted to a SAS TIME value. All others, such as YEAR, MONTH, or DAY, are converted to a SAS DATETIME value.

***LIBNAME Statement: Default SAS Formats for Informix Data Types***
Informix Column Type	Default SAS Format
CHAR(n)	$n
DATE	DATE9.
DATETIME**	DATETIME24.5
DECIMAL	none
DOUBLE PRECISION	none
FLOAT	none
INTEGER	none
INTERVAL	$n
MONEY	none
NCHAR(n)	$n NLS support required
NUMERIC	none
NVARCHAR(m,n)*	$m NLS support required
REAL	none
SERIAL	none
SMALLFLOAT	none
SMALLINT	none
TEXT*	$n
VARCHAR(m,n)*	$m
Only supported by Informix-Online databases *If the Informix field qualifier specifies either HOUR, MINUTE, SECOND, or FRACTION as the largest unit, the value is converted to a SAS TIME value. All others, such as YEAR, MONTH, or DAY, are converted to a SAS DATETIME value.

The following table shows the default Informix data types that the LIBNAME statement applies to SAS variable formats during output operations.

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

$w.
CHAR(w).

w. with SAS format name of NULL FLOAT

w.d with SAS format name of NULL FLOAT

all other numerics FLOAT

datetimew.d
DATETIME YEAR TO FRACTION(5)

datew. DATE

time. DATETIME HOUR TO FRACTION(5)

***LIBNAME Statement: Default Informix Data Types for SAS Variable Formats***
SAS Variable Format	Informix Data Type
$w.	CHAR(w).
w. with SAS format name of NULL	FLOAT
w.d with SAS format name of NULL	FLOAT
all other numerics	FLOAT
datetimew.d	DATETIME YEAR TO FRACTION(5)
datew.	DATE
time.	DATETIME HOUR TO FRACTION(5)

SQL Procedure Pass-Through Facility Data Conversions

The SQL Procedure Pass-Through Facility uses the same default conversion formats as the LIBNAME statement. See LIBNAME Statement Data Conversions for the conversion tables.

Chapter Contents
Previous
Next
Top of Page