Previous Page | Next Page

SAS/ACCESS Interface to Sybase IQ

Data Types for Sybase IQ


Overview

Every column in a table has a name and a data type. The data type tells Sybase IQ how much physical storage to set aside for the column and the form in which the data is stored. This information includes information about Sybase IQ data types, null and default values, and data conversions.

For more information about Sybase IQ data types and to determine which data types are available for your version of Sybase IQ, see your Sybase IQ Reference Manual.

SAS/ACCESS Interface to Sybase IQ does not directly support any data types that are not listed below. Any columns using these types are read into SAS as character strings.


String Data

CHAR(n)

specifies a fixed-length column for character string data. The maximum length is 32,768 characters. If the length is greater than 254, the column is a long-string column. SQL imposes some restrictions on referencing long-string columns. For more information about these restrictions, see your Sybase IQ documentation.

VARCHAR(n)

specifies a varying-length column for character string data. The maximum length is 32,768 characters. If the length is greater than 254, the column is a long-string column. SQL imposes some restrictions on referencing long-string columns. For more information about these restrictions, see your Sybase IQ documentation.

LONG VARCHAR(n)

specifies a varying-length column for character string data. The maximum size is limited by the maximum size of the database file, which is currently 2 gigabytes.


Numeric Data

BIGINT

specifies a big integer. Values in a column of this type can range from -9223372036854775808 to +9223372036854775807.

SMALLINT

specifies a small integer. Values in a column of this type can range from -32768 through +32767.

INTEGER

specifies a large integer. Values in a column of this type can range from -2147483648 through +2147483647.

TINYINT

specifies a tiny integer. Values in a column of this type can range from 0 to 255.

BIT

specifies a Boolean type. Values in a column of this type can be either 0 or 1. Inserting any nonzero value into a BIT column stores a 1 in the column.

DOUBLE | DOUBLE PRECISION

specifies a floating-point number that is 64 bits long. Values in a column of this type can range from -1.79769E+308 to -2.225E-307 or +2.225E-307 to +1.79769E+308, or they can be 0. This data type is stored the same way that SAS stores its numeric data type. Therefore, numeric columns of this type require the least processing when SAS accesses them.

REAL

specifies a floating-point number that is 32 bits long. Values in a column of this type can range from approximately -3.4E38 to -1.17E-38 and +1.17E-38 to +3.4E38.

FLOAT

specifies a floating-point number. If you do not supply the precision, the FLOAT data type is the same as the REAL data type. If you supply the precision, the FLOAT data type is the same as the REAL or DOUBLE data type, depending on the value of the precision. The cutoff between REAL and DOUBLE is platform-dependent, and it is the number of bits that are used in the mantissa of the single-precision floating-point number on the platform.

DECIMAL | DEC | NUMERIC

specifies a fixed-point decimal number. The precision and scale of the number determines the position of the decimal point. The numbers to the right of the decimal point are the scale, and the scale cannot be negative or greater than the precision. The maximum precision is 126 digits.


Date, Time, and Timestamp Data

SQL date and time data types are collectively called datetime values. The SQL data types for dates, times, and timestamps are listed here. Be aware that columns of these data types can contain data values that are out of range for SAS.

DATE

specifies date values. The range is 01-01-0001 to 12-31-9999. The default format YYYY-MM-DD--for example, 1961-06-13.

TIME

specifies time values in hours, minutes, and seconds to six decimal positions: hh:mm:ss[.nnnnnn]. The range is 00:00:00.000000 to 23:59:59.999999. However, due to the ODBC-style interface that SAS/ACCESS Interface to Sybase IQ uses to communicate with the Sybase IQ Performance Server, any fractional seconds are lost in the transfer of data from server to client.

TIMESTAMP

combines a date and time in the default format of yyyy-mm-dd hh:mm:ss[.nnnnnn]. For example, a timestamp for precisely 2:25 p.m. on January 25, 1991, would be 1991-01-25-14.25.00.000000. Values in a column of this type have the same ranges as described for DATE and TIME.


Sybase IQ Null Values

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

You can define a column in a Sybase IQ table so that it requires data. To do this in SQL, you specify a column as NOT NULL, which tells SQL to allow only a row to be added to a table if a value exists for the field. For example, NOT NULL assigned to the CUSTOMER field in the SASDEMO.CUSTOMER table does not allow a row to be added unless there is a value for CUSTOMER. When creating a table with SAS/ACCESS, you can use the DBNULL= data set option to indicate whether NULL is a valid value for specified columns.

You can also define Sybase IQ columns as NOT NULL DEFAULT. For more information about using the NOT NULL DEFAULT value, see your Sybase IQ Reference Manual.

Knowing whether a Sybase IQ column allows NULLs or whether the host system supplies a default value for a column that is defined as NOT NULL DEFAULT can help you write selection criteria and enter values to update a table. Unless a column is defined as NOT NULL or NOT NULL DEFAULT, it allows NULL values.

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 Sybase IQ assigns to SAS variables to read from a Sybase IQ table when using the Overview of the LIBNAME Statement for Relational Databases. These default formats are based on Sybase IQ column attributes.

LIBNAME Statement: Default SAS Formats for Sybase IQ Data Types
Sybase IQ Data Type SAS Data Type Default SAS Format
CHAR(n)* character $n.
VARCHAR(n)* character $n.
LONG VARCHAR(n)* character $n.
BIGINT numeric 20.
SMALLINT

TINYINT

numeric

numeric

6.

4.

INTEGER numeric 11.
BIT numeric 1.
DOUBLE numeric none
REAL numeric none
FLOAT numeric none
DECIMAL(p,s) numeric m.n
NUMERIC(p,s) numeric m.n
TIME numeric TIME8.
DATE numeric DATE9.
TIMESTAMP numeric DATETIME25.6
* n in Sybase IQ data types is equivalent to w in SAS formats.

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

LIBNAME Statement: Default Sybase IQ Data Types for SAS Variable Formats
SAS Variable Format Sybase IQ Data Type
m.n DECIMAL(p,s)
other numerics DOUBLE
$n. VARCHAR(n)*
datetime formats TIMESTAMP
date formats DATE
time formats TIME
* n in Sybase IQ data types is equivalent to w in SAS formats.

Previous Page | Next Page | Top of Page