SAS/ACCESS Software for Relational Databases: Reference

SYBASE Data Types

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. SYBASE data types fall into four categories: types for character data, types for numeric data, types for abstract values, and user-defined data types. Each of these types is described in the following sections.

Note: SAS/ACCESS does not support the following SYBASE data types: BINARY, VARBINARY, IMAGE, NCHAR(n), and NVARCHAR(n). SAS/ACCESS provides an error message when it attempts to read a table that has at least one column that uses an unsupported data type. [cautionend]

Character Data

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

CHAR(n): CHAR(n) is a character string that can have 1 to 255 letters, symbols, and numbers. You specify the maximum length of the string with n. Storage size is also n, regardless of the actual entry length.
VARCHAR(n): VARCHAR(n) is a varying-length character string that can have 1 to 255 letters, symbols, and numbers. You specify the maximum length of the string with n. Storage size is the actual entry length.
TEXT: TEXT stores character data of variable length up to two gigabytes. SAS supports the TEXT data type provided in SYBASE; however, SAS only allows a maximum of 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.

Abstract Data

SYBASE date and money data types are abstract data types and are described in this section. Refer to your documentation on Transact-SQL for more information about abstract data types.

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 minutes past midnight. Values can range from January 1, 1753 to December 31, 9999.

DATETIME values are input as quoted character strings in various alphabetic or numeric formats. Time data must be entered in the prescribed order (hours; minutes; seconds; milliseconds; AM, am, PM, pm) and 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

TIMESTAMP data is used by SAS in UPDATE mode. If you select a column that contains TIMESTAMP data for input into SAS, the values are displayed in hex format.

SMALLMONEY

SMALLMONEY data is 4 bytes long and can range from -214,748.3648 to 214,748.3647. When 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. When input, a dollar sign ($) must appear before the MONEY value. For negative values, the minus sign must follow 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 USA dollars, but no form of conversion is provided.

User-Defined Data Types

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.

NULL Values

SYBASE has a special value that is called NULL. NULL means that a value in a row is not known or is missing; it does not mean that the value is blank or zero. It is analogous to the SAS System's 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 of the columns for a table to be NULL except for CUSTOMER. In this case, SYBASE only accepts a row that 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);

LIBNAME Statement Data Conversions

LIBNAME Statement: Default SAS Formats for SYBASE Server Data Types shows the default SAS System variable formats that the libname statement assigns to SYBASE data types during input operations.

LIBNAME Statement: Default SAS Formats for SYBASE Server Data Types
SYBASE Column Type SAS Data Type Default SAS Format

CHAR(n ) character $n. (n <= 255)
$255. (n > 255)

VARCHAR(n ) character $n. (n <= 255)
$255. (n > 255)

TEXT character $n. (n <= 32,767)
$32,767. (n > 32,767)

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

SMALLDATETIME numeric DATETIME22.3

DATETIME numeric DATETIME22.3

TIMESTAMP hex $HEXw

***LIBNAME Statement: Default SAS Formats for SYBASE Server Data Types***
SYBASE Column Type	SAS Data Type	Default SAS Format
CHAR(n )	character	$n. (n <= 255) $255. (n > 255)
VARCHAR(n )	character	$n. (n <= 255) $255. (n > 255)
TEXT	character	$n. (n <= 32,767) $32,767. (n > 32,767)
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
SMALLDATETIME	numeric	DATETIME22.3
DATETIME	numeric	DATETIME22.3
TIMESTAMP	hex	$HEXw

LIBNAME STATEMENT: Default SYBASE Data Types for SAS Variable Formats shows the default SYBASE data types that the LIBNAME statement assigns to SAS variable formats during ouput operations.

LIBNAME STATEMENT: Default SYBASE Data Types for SAS Variable Formats
SAS Variable Format SYBASE Data Type

$w., $CHARw., $VARYINGw., $HEXw. VARCHAR(w)

any datetime, date, or time format DATETIME

any numeric with a SAS format name of w.d or w. NUMERIC(p,s)

any other numeric FLOAT

***LIBNAME STATEMENT: Default SYBASE Data Types for SAS Variable Formats***
SAS Variable Format	SYBASE Data Type
$w., $CHARw., $VARYINGw., $HEXw.	VARCHAR(w)
any datetime, date, or time format	DATETIME
any numeric with a SAS format name of w.d or w.	NUMERIC(p,s)
any other numeric	FLOAT

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

ACCESS Procedure Data Conversions

PROC ACCESS: Default SAS Formats for SYBASE Server Data Types shows the default SAS System variable formats that the ACCESS procedure assigns to SYBASE data types.

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

***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.

Note: The DECIMAL, NUMERIC, and TEXT data types are not supported in PROC ACCESS. The TIMESTAMP data type is not displayed in PROC ACCESS. [cautionend]

DBLOAD Procedure Data Conversions

PROC DBLOAD: Default SYBASE Data Types for SAS Variable Formats shows the default SYBASE data types that the DBLOAD procedure assigns to SAS variable formats.

PROC DBLOAD: Default SYBASE Data Types for SAS Variable Formats
SAS Variable Format SYBASE Data Type

$w., $CHARw., $VARYINGw., $HEXw. VARCHAR(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

***PROC DBLOAD: Default SYBASE Data Types for SAS Variable Formats***
SAS Variable Format	SYBASE Data Type
$w., $CHARw., $VARYINGw., $HEXw.	VARCHAR(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.

Inserting TEXT into SYBASE from SAS

TEXT data can only be inserted into a SYBASE table by using the BULK= data set option, as in the following example:

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

If the BULK option is not used, you will receive the following 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.

National Language Support for SYBASE

To support output and update processing from SAS into SYBASE in languages other than English, special setup steps are required so that date, time, and datetime values can be processed correctly. In SAS, you must ensure that the DFLANG= system option is set to the correct language. This can be globally set by the system administrator or set by a user within a single SAS session. In SYBASE, the default client language, set in the locales.dat file, must match the language used in SAS.

Chapter Contents
Previous
Next
Top of Page