Chapter Contents

Previous

Next

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	DATETIME20.
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
datew.		DATE (table note 1)
time.		HOUR

TABLE NOTE 1:  

The length of the date value determines the precision. 

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

Copyright © 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.