Data Types for SAP ASE

Overview

Every column in a table has a name and a data type. The data type indicates to the DBMS how much physical storage to reserve for the column and the format in which the data is stored. This section includes information about SAP ASE data types, null values, and data conversions, and also explains how to insert text into SAP ASE from SAS.

SAS/ACCESS does not support these SAP ASE data types: BINARY, VARBINARY, IMAGE, NCHAR(n), and NVARCHAR(n). SAS/ACCESS provides an error message when it tries to read a table that has at least one column that uses an unsupported data type.

Supported SAP ASE Data Types

Here are the supported SAP ASE data types for SAS/ACCESS Interface to SAP ASE:

• Character data:

  CHAR(n)	TEXT
  VARCHAR(n)

  Note: You must enclose all character data in single or double quotation marks.
• Numeric data:

  NUMERIC(p,s)	TINYINT
  DECIMAL(p,s)	SMALLINT
  REAL	INT
  FLOAT	BIT

  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 money data:

  DATE	TIMESTAMP
  TIME	SMALLMONEY
  SMALLDATETIME	MONEY
  DATETIME

User-Defined Data

You can supplement the SAP ASE system data types by specifying your own data types with the SAP ASE system procedure sp_addtype. When you specify your own data type for a column, you can specify a default value (other than NULL) for it and specify a range of allowable values for it.

SAP ASE Null Values

SAP ASE has a special value that is called NULL. A This value indicates an absence of information and is analogous to a SAS missing value. When SAS/ACCESS reads an SAP ASE NULL value, it interprets it as a SAS missing value.

By default, SAP ASE columns are specified as NOT NULL. NOT NULL tells SAP ASE not to add a row to the table unless the row has a value for the specified column.

If you want a column to accept NULL values, you must explicitly specify it as NULL. Here is an example of a CREATE TABLE statement that specifies all table columns as NULL except CUSTOMER. In this case, SAP ASE accepts a row only if it contains a value for CUSTOMER.

create table CUSTOMERS
   (CUSTOMER        char(8)    not null,
    STATE           char(2)        null,
    ZIPCODE         char(5)        null,
    COUNTRY         char(20)       null,
    TELEPHONE       char(12)       null,
    NAME            char(60)       null,
    CONTACT         char(30)       null,
    STREETADDRESS   char(40)       null,
    CITY            char(25)       null,
    FIRSTORDERDATE  datetime       null);

When you create an SAP ASE 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 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 ASE assigns to SAS variables when using the LIBNAME statement to read from an SAP ASE table. These default formats are based on SAP ASE column attributes.

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

You can override these default data types by using the DBTYPE= data set option.

ACCESS Procedure Data Conversions

This table shows the default SAS variable formats that SAS/ACCESS assigns to SAP ASE data types when you use the ACCESS procedure.

The ACCESS procedure also supports SAP ASE user-defined data types. The ACCESS procedure uses the SAP ASE data type on which a user-defined data type is based in order to assign a default SAS format for columns.

The DECIMAL, NUMERIC, and TEXT data types are not supported in PROC ACCESS. The TIMESTAMP data type is not displayed in PROC ACCESS.

DBLOAD Procedure Data Conversions

This table shows the default SAP ASE data types that SAS/ACCESS assigns to SAS variable formats when you use the DBLOAD procedure.

The DBLOAD procedure also supports SAP ASE user-defined data types. Use the TYPE= statement to specify a user-defined data type.

Data Returned as SAS Binary Data with Default Format $HEX

• BINARY
• VARBINARY
• IMAGE

Data Returned as SAS Character Data

• NCHAR
• NVARCHAR

Inserting TEXT into SAP ASE from SAS

You can insert only TEXT data into an SAP ASE table by using the BULKLOAD= data set option, as in this example:

data yourlib.newtable(bulkload=yes);
   set work.sasbigtext;
run;  

If you do not use the BULKLOAD= option, you receive this error message:

ERROR: Object not found in database. Error Code: -2782
An untyped variable in the PREPARE statement 'S401bcf78'
is being resolved to a TEXT or IMAGE type.
This is illegal in a dynamic PREPARE statement.