SAS/ACCESS Interface to Sybase

Data Types for Sybase

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 Sybase data types, null values, and data conversions, and also explains how to insert text into Sybase from SAS.

SAS/ACCESS does not support these Sybase 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.

Character Data

You must enclose all character data in single or double quotation marks.

CHAR(n): CHAR(n) is a character string that can contain letters, symbols, and numbers. Use n to specify the maximum length of the string, which is the currently set value for the Adaptive Server page size (2K, 4K, 8K, or 16K). Storage size is also n, regardless of the actual entry length.
VARCHAR(n): VARCHAR(n) is a varying-length character string that can contain letters, symbols, and numbers. Use n to specify the maximum length of the string, which is the currently set value for the Adaptive Server page size (2K, 4K, 8K, or 16K). Storage size is the actual entry length.
TEXT: TEXT stores character data of variable length up to two gigabytes. Although SAS supports the TEXT data type that Sybase provides, it allows a maximum of only 32,767 bytes of character data.

Numeric Data

NUMERIC(p,s), DECIMAL(p,s): Exact numeric values have specified degrees of precision (p) and scale (s). NUMERIC data can have a precision of 1 to 38 and scale of 0 to 38, where the value of s must be less or equal to than the value of p. The DECIMAL data type is identical to the NUMERIC data type. The default precision and scale are (18,0) for the DECIMAL data type.
REAL, FLOAT: Floating-point values consist of an integer part, a decimal point, and a fraction part, or scientific notation. The exact format for REAL and FLOAT data depends on the number of significant digits and the precision that your machine supports. You can use all arithmetic operations and aggregate functions with REAL and FLOAT except modulus. The REAL (4-byte) range is approximately 3.4E-38 to 3.4E+38, with 7-digit precision. The FLOAT (8-byte) range is approximately 1.7E-308 to 1.7E+308, with 15-digit precision.
TINYINT, SMALLINT, INT: Integers contain no fractional part. The three-integer data types are TINYINT (1 byte), which has a range of 0 to 255; SMALLINT (2 bytes), which has a range of -32,768 to +32,767; and INT (4 bytes), which has a range of -2,147,483,648 to +2,147,483,647.
BIT: BIT data has a storage size of one bit and holds either a 0 or a 1. Other integer values are accepted but are interpreted as 1. BIT data cannot be NULL and cannot have indexes defined on it.

Date, Time, and Money Data

Sybase date and money data types are abstract data types. See your documentation on Transact-SQL for more information about abstract data types.

DATE

DATE data is 4 bytes long and represents dates from January 1, 0001, to December 31, 9999.

TIME

TIME data is 4 byes long and represents times from 12:00:00 AM to 11:59:59:999 PM.

SMALLDATETIME

SMALLDATETIME data is 4 bytes long and consists of one small integer that represents the number of days after January 1, 1900, and one small integer that represents the number of minutes past midnight. The date range is from January 1, 1900, to December 31, 2079.

DATETIME

DATETIME data has two 4-byte integers. The first integer represents the number of days after January 1, 1900, and the second integer represents the number of milliseconds past midnight. Values can range from January 1, 1753, to December 31, 9999.

You must enter DATETIME values as quoted character strings in various alphabetic or numeric formats. You must enter time data in the prescribed order (hours, minutes, seconds, milliseconds, AM, am, PM, pm), and you must include either a colon or an AM/PM designator. Case is ignored, and spaces can be inserted anywhere within the value.

When you input DATETIME values, the national language setting determines how the date values are interpreted. You can change the default date order with the SET DATEFORMAT statement. See your Transact-SQL documentation for more information.

You can use Sybase built-in date functions to perform some arithmetic calculations on DATETIME values.

TIMESTAMP

SAS uses TIMESTAMP data in UPDATE mode. If you select a column that contains TIMESTAMP data for input into SAS, values display in hexadecimal format.

SMALLMONEY

SMALLMONEY data is 4 bytes long and can range from -214,748.3648 to 214,748.3647. When it is displayed, it is rounded up to two places.

MONEY

MONEY data is 8 bytes long and can range from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. You must include a dollar sign ($) before the MONEY value. For negative values, you must include the minus sign after the dollar sign. Commas are not allowed.

MONEY values are accurate to a ten-thousandth of a monetary unit. However, when they are displayed, the dollar sign is omitted and MONEY values are rounded up to two places. A comma is inserted after every three digits.

You can store values for currencies other than U.S. dollars, but no form of conversion is provided.

User-Defined Data

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

Sybase Null Values

Sybase 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 a Sybase NULL value, it interprets it as a SAS missing value.

By default, Sybase columns are defined as NOT NULL. NOT NULL tells Sybase 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 define it as NULL. Here is an example of a CREATE TABLE statement that defines all table columns as NULL except CUSTOMER. In this case, Sybase 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 a Sybase 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 Sybase 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 assigns to SAS variables when using the LIBNAME statement to read from a Sybase table. These default formats are based on Sybase column attributes.

LIBNAME Statement: Default SAS Formats for Sybase Server Data Types
Sybase Column Type	SAS Data Type	Default SAS Format
CHAR(n )	character	$n
VARCHAR(n )	character	$n
TEXT	character	$n. (where n is the value of the DBMAX_TEXT= option)
BIT	numeric	1.0
TINYINT	numeric	4.0
SMALLINT	numeric	6.0
INT	numeric	11.0
NUMERIC	numeric	w, w.d (if possible)
DECIMAL	numeric	w, w.d (if possible)
FLOAT	numeric
REAL	numeric
SMALLMONEY	numeric	DOLLAR12.2
MONEY	numeric	DOLLAR24.2
DATE*	numeric	DATE9.
TIME*	numeric	TIME12.
SMALLDATETIME	numeric	DATETIME22.3
DATETIME	numeric	DATETIME22.3
TIMESTAMP	hexadecimal	$HEXw
* If a conflict might occur between the Sybase and SAS value for this data type, use SASDATEFMT= to specify the SAS format. ** Where n specifies the current value for the Adaptive Server page size.

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

LIBNAME STATEMENT: Default Sybase Data Types for SAS Variable Formats
SAS Variable Format	Sybase Data Type
$w., $CHARw., $VARYINGw., $HEXw.	VARCHAR(w)
DOLLARw.d	SMALLMONEY (where w < 6) MONEY (where w >= 6)
datetime format	DATETIME
date format	DATE
time format	TIME
any numeric with a SAS format name of w.d (where d > 0 and w > 10) or w.	NUMERIC(p,s)
any numeric with a SAS format name of w.d (where d = 0 and w < 10)	TINYINT (where w < 3) SMALLINT (where w < 5) INT (where w < 10)
any other numeric	FLOAT

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

ACCESS Procedure Data Conversions

The following table shows the default SAS variable formats that SAS/ACCESS assigns to Sybase data types when you use the ACCESS procedure.

PROC ACCESS: Default SAS Formats for Sybase Server Data Types
Sybase Column Type	SAS Data Type	Default SAS Format
CHAR(n )	character	$n. (n <= 200) $200. (n > 200)
VARCHAR(n )	character	$n. (n <= 200) $200. (n > 200)
BIT	numeric	1.0
TINYINT	numeric	4.0
SMALLINT	numeric	6.0
INT	numeric	11.0
FLOAT	numeric	BEST22.
REAL	numeric	BEST11.
SMALLMONEY	numeric	DOLLAR12.2
MONEY	numeric	DOLLAR24.2
SMALLDATETIME	numeric	DATETIME21.2
DATETIME	numeric	DATETIME21.2

The ACCESS procedure also supports Sybase user-defined data types. The ACCESS procedure uses the Sybase 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 does not display in PROC ACCESS.

DBLOAD Procedure Data Conversions

The following table shows the default Sybase data types that SAS/ACCESS assigns to SAS variable formats when you use the DBLOAD procedure.

PROC DBLOAD: Default Sybase Data Types for SAS Variable Formats
SAS Variable Format	Sybase Data Type
$w., $CHARw., $VARYINGw., $HEXw.	CHAR(w)
w.	TINYINT
w.	SMALLINT
w.	INT
w.	FLOAT
w.d	FLOAT
IBw.d, PIBw.d	INT
FRACT, E format, and other numeric formats	FLOAT
DOLLARw.d, w<=12	SMALLMONEY
DOLLARw.d, w>12	MONEY
any datetime, date, or time format	DATETIME

The DBLOAD procedure also supports Sybase 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 Sybase from SAS

You can insert only TEXT data into a Sybase 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.

Top of Page