Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar

Search for    in all of
view all search results right arrow
Support > Technical Documents > Document Types > Partner Certification Report > Certification Report 383: SAS Foundation v9.3: SAS...

Certification Report 383: SAS Foundation v9.3: SAS/BASE, SAS/ACCESS Interface to Sybase IQ, SAS/ACCESS Interface to ODBC with Sybase IQ v15.4

SAS Foundation v9.3 and SAS/ACCESS Interface for Sybase IQ connect to Sybase Enterprise IQ v15.4 as a data source.
RSS Feed
Report 383



This section describes each of the products tested and how they were used during testing. For more information about any of the products, please refer to the product literature. This report documents only the platforms and products tested for certification. Other possible combinations of these products and platforms have not been certified by Sybase. For more information about Sybase IQ, including known issues not encountered during this certification or those occurring on other platforms, see the Sybase IQ Release bulletin for the specific platform.

  • Sybase IQ Enterprise Edition server version 15.4 (hereafter called "Sybase IQ") served as a data source and target and provided the Sybase ODBC drivers for connectivity to SAS. The Sybase IQ tested was a 64-bit application installed on a SPARC Solaris 2.10 server machine. Data from the iqdemo database installed with Sybase IQ as well as datatype and other table data created in Sybase IQ were used for testing.
  • Sybase IQ Network Client 64-bit version 15.4 (hereafter called the "Sybase IQ Client") was used for Sybase ODBC connectivity between SAS and Sybase IQ. If Sybase IQ (server) and SAS are installed on different workstations, the Sybase IQ Client must be installed on the SAS workstation to enable connectivity to the Sybase IQ server from SAS. In this case, the Sybase IQ server was installed on the same workstation as SAS, thus the Sybase IQ Client installation was not necessary.
  • Adaptive Server IQ ODBC driver version 12.0 (hereafter called "Sybase IQ ODBC driver") provided connectivity between SAS and Sybase IQ. The driver is a 64-bit ODBC driver that is installed with the Sybase IQ server product on Solaris.
  • SAS version 9.3 (hereafter called "SAS 9.3") business intelligence (BI) software served as a client application to Sybase IQ. The SAS Base and SAS/Access for ODBC software were tested. Specifically, the SAS Query, Library, Table Editor, Report Editor and Program Editor tools were tested with Sybase IQ data.
  • SAS Foundation version 9.3 (hereafter called 'SAS 9.3') provided business intelligence software and served as a client application to Sybase IQ.
    • SAS Base (hereafter called 'SAS/Base') provided the foundation for all SAS solutions. SAS/Base provided the client applications to Sybase IQ. Specifically, the SAS Query, Library, Table Editor, Report Editor and Program Editor tools were tested with Sybase IQ data.
    • SAS/ACCESS Interface to Sybase IQ (hereafter called 'SAS/Access Sybase IQ') provided the client connectivity to Sybase IQ as it's paired with the Sybase IQ Client ODBC drivers. While this interface uses ODBC connectivity, it differs from the SAS/Access Interface to ODBC as it is tuned specifically for Sybase IQ.
    • SAS/ACCESS Interface to ODBC (hereafter called 'SAS/Access ODBC') provided generic ODBC client connectivity to Sybase IQ as it's paired with the Sybase IQ Client ODBC drivers.

Click here for trademark and registration information.

For more detailed information about the machines used in this environment, see the section Test Environment later in this report.

The following diagram illustrates the test environment.

SAS environment with Sybase IQ

Back to Contents

Tests Performed

This section contains a detailed explanation of the tests performed.

    • Installed Sybase IQ server, including the 64-bit Sybase IQ native Open Client driver, on a SPARC Sun Solaris version 2.10 workstation.
    • Configured the Sybase IQ default GROUPO user object for login connectivity. This involved using Sybase Central to modify the properties for the GROUPO object.
    • Installed the SAS 9.3 Base software and SAS/ACCESS Interface to Sybase IQ in a different directory on the same Solaris workstation.
    • Configured SAS 9.3 security. This involved running the sassetup script.
    • Configured SAS 9.3 to access Sybase IQ using the Sybase IQ Open Client driver. This involves editing the sasenv and sasenv_local files to include the Sybase IQ environment information. Detailed information about the SAS configuration is available in Setup and Configuration section later in this report.
    • Launched SAS 9.3 and Sybase IQ.
    • Used the SAS Library tool to create a library for Sybase IQ data. For information about a problem related creating libraries with Sybase IQ data, see Test Note 5 in the the Test Notes section later in this report.
    • Used the SAS Library tool to display data in the tables available under the Sybase IQ Library and used the Library option to view information about the columns. For more information about the SAS and Sybase IQ datatype mappings, see Test Note 12.
    • Used the SAS Query tool to build SQL statements and retrieve Sybase IQ data. The SQL statements tested included complex queries with joins, distinct selects, counts and groupings.
    • Used the SAS Program Editor to submit and process SQL statements in passthru mode. The libname and proc sql statements were used as SAS programming implementations. For more information about libname and proc sql statements, see "Using the SAS Program Editor" in the Retrieving Sybase IQ Data into SAS section of this report and the SAS product documentation. For information about SQL statements that must be run in passs-through mode, see Test Note 3.
    • Tested pass-through mode syntax in the SAS Program Editor to create a table and load data into it from a text file. For information about pass-through syntax, see Test Note 2.
    • Used the SAS Table Editor to retrieve, insert, update and delete Sybase IQ data. See Test Note 4 for information about inserting date and time values.
    • Used the SAS Export wizard to export data from Sybase IQ into a .csv file. Note that the wizard does include the column headings in the .csv files it creates.
    • Evaluated SAS and Sybase IQ for the following functions:
      • complex query handling
      • error handling
      • minimum and maximum value handling
      • multiple table join handling
      • handling of the following Sybase IQ data types, including null values: integer, small integer, decimal, float, real, date, time, timestamp, datetime, double, character and varchar.

      Note that SAS classifies all data types as either character or numeric. For more information about how SAS handles data types, see Test Note 12 and the the SAS product documentation.

Back to Contents

Setup and Configuration

This section describes how to set up the test environment. It contains information about installing, configuring and verifying Sybase IQ, the Sybase IQ ODBC driver connectivity and SAS. Sybase recommends performing the tasks in the order presented here for easier verification and troubleshooting. For detailed information about the host machine and software versions, see the Test Environment section later in this report.

A summary of the tasks included follows:

Installing Sybase IQ, Creating the iqdemo Database, and Verifying

Note: These installation instructions assume you are installing Sybase IQ into a directory on a Sun Solaris workstation in which no other Sybase products are installed. Installing Sybase IQ into the same directory with other Sybase products, including an earlier version of Sybase IQ, is outside the scope of this report and may result in issues not identified during this certification test.

  1. Install Sybase IQ server according to the installation program prompts and Sybase IQ product documentation.
  2. After installation completes, set the necessary environment variables for Sybase IQ by changing to the $SYBASE/IQ-15_4 directory and executing the IQ-15_4.csh (or .sh) environment variable script.
  3. Create the sample iqdemo database for Sybase IQ by changing into the $SYBASE/IQ-15_4/demo directory and executing the script. This script will create the iqdemo database, populate it with sample tables and data and will start it within an engine named <hostname>_iqdemo, the default naming convention. Data in this iqdemo database was used in testing and in the examples that follow in this report.
  4. Verify the iqdemo database is running properly by logging into Sybase IQ using dbisql, which is an interactive SQL utility installed in the $SYBASE/bin64 directory. Launch dbisql by entering the following command:
    $SYBASE/bin64/dbisql -c "uid=dba;pwd=sql;eng=<hostname>_iqdemo" -nogui
    where <hostname> is the name of the workstation on which Sybase IQ is running and -nogui is the flag that runs dbisql in command line mode or exclude this flag to run in gui mode.
  5. When you successfully connect to the dbisql application and the command prompt appears, enter the following command as shown:
    select * from GROUPO.Customers;
    The contents of the Customers table within the Sybase IQ iqdemo database should return. This demonstrates successful installation and operation of Sybase IQ and the iqdemo database.

Modifying the GROUPO user object to enable login capabilities

This task involves using the Sybase Central tool to modify the properties associated with the GROUPO group object. The goal is to change the existing GROUPO object from a Group object to a User object, thus enabling login capabilities for it. This is desirable because the sample tables installed during the creation of the iqdemo database are all owned by the GROUPO group. This is a problem because SAS client applications do not provide a mechanism to access tables outside the Sybase IQ login's ownership scope and tables cannot be owner qualified for access. Thus, if the SAS client application logs into Sybase IQ with the "dba" user, only tables owned by dba can be accessed and attempts to qualify tables with the group "GROUPO" will fail.

  1. Start Sybase Central by changing into the $SYBASE/IQ-15_4/shared/sybcentral60 directory and executing the script from a command prompt.
  2. Connect to the iqdemo database using the "dba" login.
  3. Highlight the Users & Groups folder from within the iqdemo folder located in the left Sybase Central panel. Once highlighted, the right panel will display available users and groups within the database. Locate and right-click on the GROUPO object. Select Change to User from the pop-up window.
    GROUPO User
  4. The Type value for GROUPO will reflect the change to a User object.

  5. Right-click on the GROUPO user and select Properties to enable passwords for the user. Within the GROUPO User Properties screen, select the Enable Password check box and enter a value for the password fields. Select OK to save the values.
    GROUPO Properties
  6. Launch dbisql and login as GROUPO to verify its login capabilities. See the Installing Sybase IQ, Creating the iqdemo Database, and Verifying section, step 5 above for details on invoking dbisql from a command prompt. Execute the following query to validate connectivity:

    select * from Customers;

Installing, Configuring and Verifying SAS

The following section discusses the procedures required to install SAS, configure connectivity to Sybase IQ, and verify the connectivity. For specific information on the SAS/ACCESS Interface to Sybase IQ Software, see the "LIBNAME Statement Specifics for Sybase IQ" section of the SAS/ACCESS 9.3 for Relational Databases: Reference document.


Invoke the SAS Deployment Wizard to install the licensed SAS products. Refer to the SAS 9.3 Foundation product documentation for detailed instructions.

  1. The Select Deployment Task panel will display. Select the default Install SAS software option for the installation and Next to continue.
  2. Install SAS

  3. The Specify SAS Home panel will display. Select Create a new SAS Home radio button and enter the full path for installation within the SAS Home field as shown below. Select Next to continue.
  4. SAS Home Directory

  5. The Select Products to Install panel will display. For the test environment, the following options were selected. Select Next to continue.
  6. Products to Install

  7. The Select SAS Foundation Products panel will display. Select the licensed products and select Next to continue. We chose Base SAS, SAS/ACCESS Interface to Sybase IQ and SAS/Access Interface to ODBC products.
  8. SAS Foundation Products

  9. The Specify SAS Installation Data File panel will display. Browse to the appropriate location of the SAS download and select Next to continue.
  10. The Select Language Support panel will display. Select the languages you are interested in installing and select Next to continue. The default language is English.
  11. The Regional Settings panel will display. English is the default Language Region. Select Next to continue.
  12. The Select Java Runtime Environment panel will display. We chose to Install the recommended Java Runtime Environment for the test environment. Select Next to continue.
  13. The Select Authentication Type panel will display. The PAM Authentication is an option on this panel for installation, but we did not install this option for the test environment. Select Next to continue.
  14. The remaining panels perform system checks and show the completion of the deployment.
  15. The Additional Resources panel is the final panel within the installation. This panel indicates the deployment is not complete until certain files within the SAS installation have setuid permissions set and are owned by root. To accomplish this, the .../SASFoundation/9.3/utilities/bin/ script should be run as root. See the "Post-Installation Configuration for User Authentication and Identification" chapter of the Configuration Guide for SAS Foundation for UNIX Environments.
  1. Run $SASROOT_INSTALL_ROOT/utilities/bin/ as the root user following the completion of the SAS installation. The $SASROOT_INSTALL_ROOT is the .../SASFoundation/9.3 directory. The script invokation will report the following:

    Performing the User Authentication setup step required by SAS.
    Attempting to setuid bit and change ownership of files:, to root
    usage: chown [-fR] owner[.group] file ...
    usage: chmod [-fR] <absolute-mode> file ...
    chmod [-fR] <ACL-operation> file ...
    chmod [-fR] <symbolic-mode-list> file ...
    where <symbolic-mode-list> is a comma-separated list of
    where <ACL-operation> is one of the following
    where <acl-specification> is a comma-separated list of ACEs
    User Authentication setup has successfully completed.

    Upon the conclusion, two scripts (sasenv, sasenv_local) will be created within the /SASFoundation/9.3/bin directory and will be used later to set the environment variables for the SAS applications.

  2. Modify the sasenv.csh (or .sh) environment script created from the prior step to include any additional environment variables. This file is located within the $SASROOT_INSTALL_ROOT/bin directory. The test environment utilized the C shell environment, thus the .csh script was modified. The variables in the sasenv and sasenv_local file are formatted for the Korn shell environment.
  3. sasenv (.csh or .sh) script - This file is created by the installation program and sets the SAS environment variables ($SAS_INSTALL_ROOT, $JAVA_HOME, $LD_LIBRARY_PATH and $SAS_ALT_DISPLAY). See the SAS documentation for more information about these variable settings. The following item was added to this script:

    setenv SAS_INSTALL_ROOT /work/SAS92/SASFoundation/9.3

    sasenv_local (.csh or .sh) script - This file is created by the installation program and should be used to set relevant variables for your environment. Make a back-up copy of the original sasenv_local.csh file in case you need to return to it. It's recommended that the contents of the IQ-15_4.csh (.sh) environment script be integrated into this script. This will ease remote client connectivity to SAS.

  1. Open the SAS application by changing to the $SAS_INSTALL_ROOT directory and entering sas. If you are running in an xterm environment, be sure to set the $DISPLAY variable first. The multiple windows that comprise the SAS application appear.
    Getting Started Panel
  2. Choose to view the Quick Start Guide (Getting Started Tutorial dialog box shown above) or close it. If a message appears saying the SAS Quick Start Guide failed to start and that "Netscape" or "export" could not be found, some environment variables may be missing or set incorrectly. Refer to the SAS documentation or the SAS support group for assistance.
  3. Exit the sas application: From the SAS Explorer window, choose File->Exit.

Installing the SAS Remote Browser Server

The Remote Browser Server is available for installation on the Windows or Linux platforms. Once the Remote Browser Server is installed on a client workstation, the server starts upon reboot and is configured to listen to events from the SAS Foundation services. When the SAS client tools like SAS Query, SAS Explorer and SAS Program Editor execute queries, the HTML result set is picked up by the Remote Browser Server and displayed within a browser interface.

  1. Download the SAS Remote Browser Server from the SAS website.
  2. Execute the installation and follow the prompts to specify the installation location.
  3. Once the install has applied the application, it will ask for the connectivity information for the Sybase IQ server and database name.
  4. The Remote Browser Server will restart at boot time and all query results will display through a browser interface where this server resides.

Configuring Connectivity between SAS and Sybase IQ

Configure the SAS environment for connectivity using the SAS/ACCESS Interface to Sybase IQ. Perform the following tasks using the $SAS_INSTALL_ROOT/sas tool:

  1. Locate and execute the Sybase IQ environment script prior to starting the "sas" tool:

    source .../IQ-15_4/IQ-15_4.csh

  2. Register the SAS/ACCESS Interface to Sybase IQ within SAS. Within the sas tool, locate the Program Editor screen and enter the following:
  3. PROC NICKNAME CAT=sashelp.core engine;
    add nickname=sybaseiq module=sasiosiq desc=”SAS/ACCESS to Sybase IQ”
    preferred eng;

    From the Program Editor toolbar, select Run -> Submit to execute.

  4. Test the connectivity to Sybase IQ using a manually defined connection within the "sas" Program Editor screen. Within the Program Editor window, enter the connection information and select Run -> Submit to execute. See the example below:
    Manual Query

    See Test Note 5 for information on connecting to Sybase IQ using an ODBC dsn and the SAS/ACCESS Interface to ODBC.

    NOTE: The libname, shown as iqtest above, can be anything, but must be used to qualify the tablename within the query. Additionally, a useful option within the SAS Program Editor is the Recall Last Submit option under the Run menu. This option allows you to retrieve the last command submitted through the Program Editor, which can then be resubmitted or modified and resubmitted. Commands submitted can be saved to external files using the File | Save As command.

    The result set will display as HTML from the Remote Browser:
    Browser Results

    The SAS Log screen will display the following information following the execution of the query:
    Log screen

  5. Create a SAS Library Function within the sas client tool to ease connectivity to Sybase IQ. The SAS Library will include connectivity information for Sybase IQ and can be used within the SAS Query Editor for easy connectivity to Sybase IQ instead of the above manual method of connectivity:
    1. Within the SAS Explorer tool, right-click on the Libraries icon and select New:
      Library Open
    2. Within the SAS: New Library screen, enter a unique library Name, select Sybase IQ from the Engine drop-down, select Enable at startup and enter the connection information within the Options field. Make sure the GROUPO user and password are used within the definition to enable access to the iqdemo sample tables. See the Modify the GROUPO user object to enable login capabilities section earlier in this report for further information about the need to enable GROUPO as a user for our testing. Test Note 5 for further Library creation information.
      SybaseIQ New Library
    3. Select OK to save the Library definition.
  6. Verify connectivity using the new SAS Library.
    1. Within the SAS: Explorer panel (sas tool), right-click on the Libraries icon and select Open.
    2. Highlight the new library (iqdemo15), right-click and select Open. The SAS: Explorer will display a list of tables within the iqdemo database:
      Table List
    3. Right-click on the Customers table within the list and select Open. SAS will query the Customers table and return the data results within a SAS: VIEWTABLE screen:
      Library Query
      This verifies successful connectivity to Sybase IQ using a SAS Library and the SAS/ACCESS Interface to Sybase IQ.

Back to Contents

Feature Example

The following example demonstrates some basic techniques used within the SAS Query Editor to create a complex query, making use of an existing SAS Library definition. The following complex query will be demonstrated, utilizing sample tables from the Sybase IQ iqdemo database:

SELECT SalesOrders.Region, Customers.State, Customers.City, Count(SalesOrders.ID)
FROM SalesOrders, Customers
WHERE SalesOrders.CustomerID = Customers.ID
GROUP BY SalesOrders.Region, Customers.State, Customers.City
ORDER BY SalesOrders.Region ASC, Customers.State ASC, Customers.City ASC

Using the SAS Query Tool

  1. Open the sas application, if it is not currently running: Change into the $SAS_INSTALL_ROOT directory and enter sas.
  2. Open the SAS Explorer window. From the Tools menu, select Query. The SAS Query SQL QUERY TABLES window opens.
  3. Select the library created for the Sybase IQ connectivity (iqdemo15) from the Table Sources window. The list of Available Tables should fill in.
    Query Table List
  4. Highlight the IQDEMO15.SalesOrders and IQDEMO15.Customers tables from the Available Tables list and select the right arrow (>) to place the tables in the Selected Tables list. Select OK to continue.
  5. The SQL QUERY COLUMNS window appears and displays all the columns from the SalesOrders and Customers tables within the Available Columns panel on the left. Highlight the SalesOrders.Region, Customers.State, Customers.City, SalesOrders.ID columns and click the arrow pointing right (>) to place the columns within the Selected Columns panel on the right:
    Join Columns
  6. To include a Distinct criteria, select View from the SAS: Query toolbar and then Distinct. The word "distinct" appears in the Selected Columns window. Note: Most of the options within the View menu are also available by right-clicking in the Selected Columns window.
  7. To include a Count function on the ID column, highlight ID from the Selected Columns panel, select the Summary Functions button and select the COUNT value. The ID entry within the Selected Columns panel will be modified to COUNT(ID) as ID1 label="COUNT(ID)".
    Join Count
  8. To include a WHERE clause, from the View menu, choose WHERE Conditions for Subset... The SAS QUERY/ WHERE EXPRESSION window will open. Select the SalesOrders.CustomerID from the Available Columns panel. An Operator pop-up window will appear. Select EQ (equals) and SalesOrders.CustomerID EQ will be entered into the Where panel as the first portion of the where clause. To complete the where clause, select Customers.ID from the Available Columns panel. The Where panel will reflect the full where clause as shown below:
    Join Where Clause
    Select OK to continue. The SQL QUERY COLUMNS window will return.
  9. To include a GROUP BY clause, select Group(s) for Summary Functions... from the View menu. The GROUP BY COLUMNS window will appear. Highlight the Region, State, City columns from the Available Columns panel. Select the right arrow (>), placing them into the Group By Columns panel. Select OK to continue. The SQL QUERY COLUMNS window will redisplay unchanged. This is normal.
  10. To include an ORDER BY clause, select Order By... from the View menu. The ORDER BY COLUMNS window will appear. Highlight the Region, State, City columns from the Available Columns panel. Select the right arrow (>), placing them into the Order By Columns panel. Select OK to continue. The SQL QUERY COLUMNS window will redisplay unchanged. This is normal.
  11. Display the full content of the query by choosing Tools -> Show Query.... from the SQL QUERY COLUMNS menu bar. The SQL QUERY window appears showing the final query:
    Final Query
  12. Select the Run Query button to execute the query. A menu with two options appears: Run immediate or Design a Report. Choose Run immediate. If the query succeeds, a browser will display on the workstation running the SAS Remote Browser Server, containing the SAS Output results from the query.
    Query Output

Back to Contents

Test Notes

This section contains information gained during testing, which may be helpful.

Test Note 1: The SAS Program Editor reports "ERROR: The SAS/ACCESS Interface to SYBASEIQ cannot be loaded. The SASSIQ code appendage could not be loaded. Check the SAS Log for possible addit8ional information." Library Error

The SAS Log reports the following errors:

SAS: Log Library Error

Resolution: The sasiq module within SAS 9.3, which is part of the SAS/ACCESS Interface to Sybase IQ, has been linked to the Sybase IQ driver. This driver was distributed with Sybase IQ through versiona 15.3. Sybase IQ v15.4 distributes a new driver version Thus when running with Sybase IQ v15.4 it cannot find the appropriate driver. To resolve the issue, create a softlink from to so that SAS can find a

ln -s /sybase/IQ-15_4/lib64/

SAS Note #47004 will be published with a description of the workaround for SAS v9.3 and Sybase IQ v15.4. Currently, SAS has slated the fix to be made in SAS v9.4.

Test Note 2: CREATE TABLE and LOAD TABLE statements Must Be Run in SAS Pass-through mode Syntax

To execute CREATE TABLE and LOAD TABLE statements against Sybase IQ through SAS, use the SAS Program Editor and submit the statements in pass-through mode syntax. This test note contains the sample syntax for creating a table and loading it with data from a text file. Attached is a sample file (region2.txt) the data from which is loaded in the following statement. To run create and load table statements using this syntax, enclose the statements in parenthesis in the execute statement as shown in the following sample.

/*** implicit pass-through uses the LIBNAME connection ***/ 
libname iqtest sybaseiq host=aelab server="aelabsun_iqdemo" database=iqdemo port=4638 user=GROUPO password=partner;
proc sql;
drop table iqtest.region2;
create table region (r_regionkey int,r_name char(25),r_comment varchar(152));

/*** explicit pass-through does not use the LIBNAME connection ***/
proc sql;
connect to sybaseiq (host=aelabsun server="aelabsun_iqdemo" database=iqdemo port=4638 user=GROUPO password=partner);
execute (load table region ( r_regionkey'|',r_name'|',r_comment '|')
from '/work/sybase/IQ-15_4/bin64/region.txt'
escapes off
quotes off
preview on
with checkpoint on) by sybaseiq;
disconnect from sybaseiq;

Test Note 3: Create View, Create Procedure, CAST, subqueries and stored procedure statements must be run in pass-through mode

The SAS application does not support the CREATE PROCEDURE or CAST SQL statements. It does support the CREATE VIEW statement, but this statement creates a view within the SAS environment. It does not create a view in Sybase IQ. Use pass-through mode to run any of these statements.

Test Note 4: Date and Time Formats in SAS (requires "T" or "D")

When inserting or updating date or time data types through SAS, you must include the letter "D" for date values or "T" for time values. Please refer to the Chapter 3, section 3.7 "Working with SAS Dates" in The Little SAS Book a Primer for more information. Following is a sample insert statement that shows how to format date and time data for insertion into Sybase IQ through the SAS Program Editor:

insert into sample_date values (1,'28FEB2005'D,'04:31:48'T);
update test_date set date_null='15DEC2006'd where row_number=1;

Test Note 5: Connecting to Sybase IQ using the Program Editor and an ODBC dsn

When connection to Sybase IQ through the Program Editor using an ODBC dsn, the following sytax is required:

libname iqtest odbc dsn=gaiq154 user=GROUPO password=partner;
proc sql;
select * from iqtest.Customers;

Note: Currently ODBC connectivity does not work within other SAS client tools. The other tools require a SAS Library be created for the ODBC connection. In SAS v9.3 this does not work. SAS case #7610823362 has been opened with SAS support for this issue. Test Issue 1 for further information.

Explanation of Statements:
libname statement - assigns a name to the Sybase IQ library for this SQL statement. Note that the SAS library defined here does not have to exist (i.e., does not have to be created first with the SAS Create Library utility). odbc indicates that this will be an ODBC-type library; dsn=gaiq154 is the name of the ODBC data source defined for Sybase IQ in the .odbc.ini file, uid=GROUPO is a valid Sybase IQ demo database user id and pwd=partner is that user's password.
proc sql statement indicates the type of statement (SQL) being submitted; in this case, the SELECT retrieves data from the Sybase IQ demo customer table. The table must be qualified with the library name assigned to Sybase IQ in the libname statement as shown here.
semi-colons Be sure to include the semi-colons as shown.

Test Note 6: Enable the IDENTITY_INSERT option in Sybase IQ to insert into tables with identity columns

To insert data into Sybase IQ tables with identity columns, you must enable the IDENTITY_INSERT option for the specific table in Sybase IQ. The following statement shows how to set the option, for example, through dbisql:

set option PUBLIC.IDENTITY_INSERT table = 'on'

where PUBLIC makes this change effective for all users permanently until it is explicitly revoked and table is the name of the table for which you want to enable the option.

Test Note 7: Using Pass-through Mode to Bulk-Insert Rows

SAS and Sybase IQ support the bulk insertion of rows through the Program Editor when the Force Updatable Cursors database option is enabled in Sybase IQ and the SAS libname statement contains the SQL_INSERT=NO option.

To perform bulk inserts, first set the Force Updatable Cursors option in Sybase IQ. For example, use dbisql to execute the following command and then exit dbisql to ensure the option setting takes effect:

set temporary option Force_Updatable_Cursors ='on';

where temporary makes the change only effective for the current user, which is recommended for security reasons with updatable cursors. Then submit the insert statements through the SAS Program Editor with a libname statement similar to the following example:

libname iqtest sybaseiq host=aelabsun server="aelabsun_iqdemo" database=iqdemo port=4638 user=GROUPO password=partner INSERT_SQL=NO;
proc sql;
insert into iqtest.region4 values (8,'insert3','test');

If the Force Updatable Cursors option is not set in the Sybase IQ database, an error similar to the following appears in the SAS log:

"WARNING: During insert: [Sybase][ODBC Driver]Error in row : [Sybase][ODBC Driver][Adaptive Server Anywhere]
Update operation attempted on a read-only cursor
NOTE: 1 row was inserted into iqtest.region4."

For additional information about libname statements with relational databases, see the LIBNAME Statement for Relational Databases section of the SAS/ACCESS for Relational Databases Reference documentation.

Test Note 8: Segmentation Violation Errors

If you encounter segmentation violation errors while using SAS utilities with Sybase IQ, the likely cause is that the wrong file from the Sybase IQ installation is being loaded. In prior version of SAS, the file from the open source ODBC driver manager needed to be loaded for SAS to work properly with Sybase IQ. However, in SAS 9.3 this no longer works. The resolution to this problem is still outstanding with SAS. Within the SAS 9.3, The SAS Explorer tool returns segmentation violation when you attempt to create a New ODBC Library. See Issue 1 for further information.

Test Note 9: SAS Log error: "Could not display help because connection to the remote browser failed."

Remote Browser Server Error

This error may occur while trying to return a result set from a query whil using one of the SAS client tools.

Resolution: Install the SAS Remote Browser Server on a Windows or Linux server. During the installation of this, it will ask for connectivity information for the Sybase IQ server and database. Once installed, the server will automatically startup at boot time. All query results will display to this server/browser interface.

Test Note 10: NULL values are represented in the SAS Remote Browser Server differently depending on the datatype.

NULL character values display as blanks. NULL date, datetime, time, timestamp values are displayed with a period (.).

Test Note 11: Changing the default decimal place format within SAS.

The default decimal place format for all Numeric datatypes within SAS is 2 decimal places. If your data has more than 2 decimal places, it is likely the values will be rounded. If this is the case, change the Data Column Attributes within SAS Remote Browser to accomodate a larger decimal place. The example below shows a DECIMAL (19,4) Sybase IQ column.

  • Display the data within the Remote Browser first. Then highlight the desired column and select Data -> Column Attributes from the toolbar within the Remote Browser as shown below.

Change Decimal Format

  • The SAS:Column Attributes window will display as shown below:
  • Decimal Column Attributes

  • Select the Drop Down for the Format field. The SAS: Format window will display. Change the Decimal field as appropriate and select OK to save and then Apply from the SAS: Column Attributes window to refresh the data display:
  • Decimal Change

Test Note 12: Sybase IQ Data Types Mapped to SAS Data Types and Formats in the SAS Library

The following table shows Sybase IQ data types as displayed as results in the Sybase DBISQL utility and the defaults SAS assigns to Sybase IQ data types when a SAS library is created for Sybase IQ. SAS assigns all data types either a numeric or character type. It is the SAS format that distinguishes the display of data. Date types are assigned as numeric. SAS calculates dates based on the number of days since January 1, 960. For more information about how SAS handles dates, refer to the SAS documentation.

To see this information, create a SAS library for the source data and then view the "Contents" of the library, select a table and choose View Columns or Open to see the actual data. *Note values displayed in the following table may be wrapped for display in the report. They did not wrap when displayed in either SAS or Sybase DBISQL.

For an explanation of SAS formats and informats, please refer to the SAS documentation.

Sybase IQ Data Type Example display in DBISQL* Default SAS Data Type Default SAS Formats** Example SAS default display
smallint 32767 numeric Length=8, Format=6, Informat=6 32767
integer -2147483647 numeric Length=8, Format=11, Informat=11 -2147483647
decimal (for example, decimal(19,4) 123456789.1230 numeric Length=8, Format=DOLLAR23.2, Informat=DOLLAR23.2 $123,456,789.12
real 339,999,995,214,436,420,000,000,000,


numeric Length=8, Format=<blank>, Informat=<blank> 3.4E38
float -7,200,000,000,000,000,000,000,000,000,000,



numeric Length=8, Format=<blank>, Informat=<blank> -7.2E75
date 9999-12-31 numeric Length=8, Format=DATE9., Informat=DATE9. 31DEC9999
time 23:59:59 numeric Length=8, Format=TIME8., Informat=TIME8. 23:59:59
datetime 1905-02-10 01:31:48.000000 numeric Length=8, Format=DATETIME25.6, Informat=DATETIME25.6 10FEB1905:01:31:48.000000
timestamp 7910-12-31 23:59:59:333000 numeric Length=8, Format=DATETIME25.6, Informat=DATETIME25.6 31DEC7910:23:59:59:333008
character (for example, char(1)) a character Length=1, Format=$1., Informat=$1. a
character (for example, char(102)) xxxxxxxxx10xxxxxxxx20xxxxxxxx30xxxxxxxx40



character Length=102, Format=$102., Informat=$102. xxxxxxxxx10xxxxxxxx20xxxxxxxx30xxxxxxxx40




varchar (for example, varchar(10) ZZZZZzzzzz character Length=10, Format=$10., Informat=$10. ZZZZZzzzzz
binary 0x1234500000 text Length=5, Format=$HEX10., Informat=$HEX10. 1234500000
varbinary 0x0123 text Length=5, Format=$HEX10., Informat=$HEX10. 0123202020

Test Note 13: Datatypes falling outside the SAS supported datatype ranges may result in rounding.

The following examples show rounding of data resulting from the Sybase IQ data values falling outside the default SAS data ranges:

Datatype Sybase IQ Value SAS Value Comments
DECIMAL(31,0) 9,999,999,999,999,999,999,999,999,999,999 9,999,999,999,999,999,635,896,294,965,248  
DECIMAL(19,4) 999,999,999,999,999.9999 1,000,000,000,000,000.00  
FLOAT 3.402823466e+38 3.4028235e38  
MONEY $922337203685477.5807 $922337203685477.0000 Sybase IQ MONEY max and min values result in the decimal places being stripped off. SMALLMONEY values are OK.
TIMESTAMP, SMALLDATETIME, DATETIME 9999-12-31- 9999-12-31-00.00.59 The max data values drop the HH:MM values.


Back to top of Notes section | Back to Contents

Issues and Incompatibilities

The following unresolvable issues were encountered during testing.

Issue 1: Cannot create ODBC Library definitions within SAS Explorer. Segmentation Violations result.

While trying to create SAS Library definitions for ODBC, a segmentation violation occurs and SAS must be restarted. The following errors are reported:

ERROR:  An exception has been encountered.
Please contact technical support and provide them with the following traceback information:
The SAS task name is [Library ]
Segmentation Violation

Traceback of the Exception:
_START_+0x4449c             - /work/AELABSUN/gaiq154/IQ-15_4/lib64/
_START_+0x43b04             - /work/AELABSUN/gaiq154/IQ-15_4/lib64/
_START_+0x44390             - /work/AELABSUN/gaiq154/IQ-15_4/lib64/
SQLGetDiagRec+0x00064       - /work/AELABSUN/gaiq154/IQ-15_4/lib64/
SQLGetDiagRec+0x00108       - /work/AELABSUN/gaiq154/IQ-15_4/lib64/
du_tkerror+0x00100          - /work/AELABSUN/SASHome/SASFoundation/9.3/sasexe/sasodb
du_tkdsrc+0x0006c           - /work/AELABSUN/SASHome/SASFoundation/9.3/sasexe/sasodb
du_dsrc+0x001f0             - /work/AELABSUN/SASHome/SASFoundation/9.3/sasexe/sasodb
sasodbcb+0x0010c            - /work/AELABSUN/SASHome/SASFoundation/9.3/sasexe/sasodbcb
zuxlibo+0x06a00             - /work/AELABSUN/SASHome/SASFoundation/9.3/sasexe/saszu2
zuxlibo+0x03b34             - /work/AELABSUN/SASHome/SASFoundation/9.3/sasexe/saszu2
zuxlibo+0x01a88             - /work/AELABSUN/SASHome/SASFoundation/9.3/sasexe/saszu2
zuxlibo+0x01338             - /work/AELABSUN/SASHome/SASFoundation/9.3/sasexe/saszu2
zuxlibo+0x00cbc             - /work/AELABSUN/SASHome/SASFoundation/9.3/sasexe/saszu2
zuxcrea+0x0015c             - /work/AELABSUN/SASHome/SASFoundation/9.3/sasexe/saszu
wuxcrea+0x00034             - /work/AELABSUN/SASHome/SASFoundation/9.3/sasexe/sasmotif
zulib_createlabel+0x010d8   - /work/AELABSUN/SASHome/SASFoundation/9.3/sasexe/saszu2
intfsio+0x00838             - /work/AELABSUN/SASHome/SASFoundation/9.3/sasexe/sasxfs
yufsio+0x00110              - /work/AELABSUN/SASHome/SASFoundation/9.3/sasexe/sasxfs
saszlasg+0x00430            - /work/AELABSUN/SASHome/SASFoundation/9.3/sasexe/saszlasg
vvtentr+0x0012c             - /work/AELABSUN/SASHome/SASFoundation/9.3/sasexe/sas
_thr_slot_offset+0x00488    - /lib/sparcv9/

Note: The Program Editor interface successfully connects using the ODBC dsn for adhoc queries. See Test Note XX for further information.

Back to Contents

Test Environment

This section provides details about the test environment.

Client Machine

Hardware or Software Description or Version

Operating Environment

Intel Core, 2 Duo CPU P8600 @ 2.40GHz

4 GB

Disk  230 GB
Operating System Microsoft Windows XP Professional v2002 SP3
SAS Remote Browser Server version 9.3

Server Machine

Hardware or Software Description or Version
Machine, processors 64-bit Sun Ultra SPARC IV
4 x 1.8 GHz processors
Memory (RAM) 32 GB
Operating System 64-bit Sun Solaris 10 with language packs installed
Disk Space 1 TB
Network Ethernet, TCP/IP
Language software Sun language packs
Sybase IQ Enterprise Edition 64-bit version 15.4
Sybase IQ Network Client 64-bit, ODBC driver ( version 12.0
SAS Foundation Software (Base)
SAS/ACCESS Interface to Sybase IQ 64-bit version
SAS/ACCESS Interface to ODBC


Back to Contents

Other Sources of Information

Sybase IQ Resource Page

Sybase provides a site dedicated to the Sybase IQ product. On this page, there are links to Data Sheets, White Papers, Webcasts / Podcasts, News, Case Studies (customer success stories), Events, Analyst Reports, the Sybase Analytics Blog, Videos, Awards, Newsletters, and Migration Resources. There is also a link on this site to a free download trial of Sybase IQ.

The Sybase IQ Resource Page is located at:

Sybase Partner Program

For more information about the Sybase Partner Program, please visit the Sybase Partner Program website.


  • The Little SAS Book, a Primer, third edition, Lora D. Delwiche and Susan J. Slaughter
  • SAS/Access 9.3 for Relational Databases: Reference, specifically the SAS/ACESS Interface to Sybase IQ section

Back to Contents

Vendor Contact Information

Vendor Name: SAS
Technical Support Number: (919) 677-8000

Back to Contents


Last Revised: Jul 26, 2012
Product: ODBC, Sybase IQ
Technical Topics: Connectivity, Data Analysis, Data Modeling, Database Admin, Data Management, Data Integration
Business or Technical: Technical
Content Id: 1098887
Infotype: Partner Certification Report

© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics