Data Types for SAP IQ

Overview

Every column in a table has a name and a data type. The data type tells SAP 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 SAP IQ data types, null and default values, and data conversions.

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

SAS/ACCESS Interface to SAP 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.

Supported SAP IQ Data Types

Here are the data types that SAS/ACCESS Interface to SAP IQ supports:

• Character data:

  CHAR(n)	VARCHAR(n)
  LONG VARCHAR(n)

• Numeric data:

  BIGINT	INTEGER
  BIT	REAL
  DECIMAL | DEC | NUMERIC	SMALLINT
  DOUBLE | DOUBLE PRECISION	TINYINT
  FLOAT

  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.
• Date, Time, and Timestamp data:
  Note: 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	TIMESTAMP
  TIME

SAP IQ Null Values

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

To indicate that NULL values are not allowed, you can specify in SQL that a column is NOT NULL. The NOT NULL designation tells SQL to reject any rows that contains NULL data values for those columns. For example, if you designate the Score column as NOT NULL for a table, then any attempt to load a row without a value for Score results in an error. When you create 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 SAP IQ columns as NOT NULL DEFAULT. For more information about using the NOT NULL DEFAULT value, see your SAP IQ documentation.

For your data, determine whether an SAP IQ column allows NULL values or the host system supplies a default value for a column that is specified as NOT NULL WITH DEFAULT. Knowing this information, you can write selection criteria and enter values to update a table. Unless a column is specified 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 SAS missing character values are handled, use the NULLCHAR= and NULLCHARVAL= data set options.

LIBNAME Statement Data Conversions

This table shows the default formats that SAS/ACCESS Interface to SAP IQ assigns to SAS variables when using the LIBNAME statement to read from an SAP IQ table. These default formats are based on SAP IQ column attributes.

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