Data Types for Informix

Overview

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. This section includes information about Informix data types, null values, and data conversions.

Supported Informix Data Types

Data Types

Here are the data types that the Informix engine supports.

Character data:

CHAR(n)

NVARCHAR(m,n)

NCHAR(n)

TEXT

VARCHAR(m,n)

BYTE

Numeric data:

DECIMAL

REAL

MONEY

SMALLFLOAT

NUMERIC

SERIAL

FLOAT

SMALLINT

DOUBLE PRECISION

INT8

INTEGER

SERIAL8

Note: When performing calculations on numeric values and when storing numeric values, SAS maintains up to 15 digits of precision. When you read values that contain more than 15 decimal digits of precision from a database into SAS, the values that SAS reads are rounded to meet this condition. When you use a large numeric value in a WHERE clause, this rounding can cause unexpected results, such as not selecting desired rows. For noncomputational purposes, such as storing ID values or credit card numbers, you can read the data in as character data. For more information, see Your Options When Choosing Your Needed Degree of Precision.

TipWhen the length value of INT8 or SERIAL8 is greater than 15, the last few digits currently do not display correctly due to a display limitation.

Date, time, and interval data:

DATE

INTERVAL

DATETIME

Informix Null Values

Informix has a special value that is called NULL. An Informix NULL value means an absence of information and is analogous to a SAS missing value. When SAS/ACCESS reads an Informix NULL value, it interprets it as a SAS missing value.

If you do not indicate a default value for an Informix column, the default value is NULL. You can specify the keywords NOT NULL after the data type of the column when you create an Informix table to prevent NULL values from being stored in the column. When you create an Informix table with SAS/ACCESS, you can use the DBNULL= data set option to indicate whether NULL is a valid value for specified columns.

For more information about how SAS handles NULL values, see Potential Result Set Differences When Processing Null Data.

To control how the DBMS handles SAS missing character values, use the NULLCHAR= and NULLCHARVAL= data set options.

LIBNAME Statement Data Conversions

This table shows the default formats that SAS/ACCESS Interface to Informix assigns to SAS variables when using the LIBNAME statement to read from an Informix table. These default formats are based on Informix column attributes. To override these default data types, use 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.
DATETIME3	DATETIME24.5
DECIMAL	m+2.n
DOUBLE PRECISION	none
FLOAT	none
INTEGER	none
INT82	none
INTERVAL	$n
MONEY	none
NCHAR(n)	$n NLS support required
NUMERIC	none
NVARCHAR(m,n)1	$m NLS support required
REAL	none
SERIAL	none
SERIAL82	none
SMALLFLOAT	none
SMALLINT	none
TEXT1	$n
VARCHAR(m,n)1	$m
1 Supported only by Informix online databases.
2 The precision of an INT8 or SERIAL8 is 15 digits.
3 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 other values (such as YEAR , MONTH , or DAY) are converted to a SAS DATETIME value.

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

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		DOUBLE
w.d with SAS format name of NULL		DOUBLE
all other numerics		DOUBLE
datetimew.d		DATETIME YEAR TO FRACTION(5)
datew.		DATE
time.		DATETIME HOUR TO SECOND

SQL Pass-Through Facility Data Conversions

The SQL pass-through facility uses the same default conversion formats as the LIBNAME statement. For conversion tables, see LIBNAME Statement Data Conversions.

Last updated: February 3, 2026