Data Types for Sybase

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 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. It 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 are displayed 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 it and define a range of allowable values for it.

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 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 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
$n2
VARCHAR(n )
character
$n2
TEXT
character
$n.2 (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
DATE1
numeric
DATE9.
TIME1
numeric
TIME12.
SMALLDATETIME
numeric
DATETIME22.3
DATETIME
numeric
DATETIME22.3
TIMESTAMP
hexadecimal
$HEXw
1If a conflict might occur between the Sybase and SAS value for this data type, use SASDATEFMT= to specify the SAS format.
2n 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 is not displayed 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.
Copyright © SAS Institute Inc. All rights reserved.