SAS/ACCESS Software for Relational Databases: Reference

ACCESS Procedure: SYBASE Specifics

The following section describes the SYBASE-specific statements that you use in the SAS/ACCESS Interface to SYBASE.

In Version 8 of the SAS/ACCESS Interface to SYBASE, you can do the following:

create, update, and use Version 8 access descriptors and view descriptors
update an access descriptor created in Version 6
create a view descriptor that is based on an access descriptor created in Version 6
use a view descriptor created in Version 6.

If you are using Version 6 of the SAS/ACCESS Interface to SYBASE, you can do the following:

create and use Version 6 access and view descriptors
use a view descriptor created in Version 8 that is based on an access descriptor created in Version 6
use an access descriptor created in Version 6 and updated in Version 8.

Note: In Version 6, you cannot use access descriptors created in Version 8, and you cannot use view descriptors created in Version 8 that are based on Version 8 access descriptors. [cautionend]

ACCESS Procedure Statements for SYBASE

To create an access descriptor, you use the DBMS=SYBASE option and these database description statements in the PROC ACCESS step: USER=, PASSWORD=, DATABASE=, SERVER=, INTERFACE=, and SYBBUFSZ=. The database description statements supply DBMS-specific information to the SAS System. These statements must immediately follow the CREATE statement.

Database description statements are required only when you create access descriptors. Because DBMS information is stored in an access descriptor, you do not need to repeat this information when you create view descriptors.

The SAS/ACCESS Interface to SYBASE uses the following procedure statements in interactive line, noninteractive, or batch mode.

PROC ACCESS <accdesc-options|viewdesc-options>;

CREATE <libref.>member-name.ACCESS|VIEW <password-option>;

UPDATE <libref.>member-name.ACCESS|VIEW <password-option>;

USER= <'>user-name<'>;

PASSWORD= |PASS= |SYBPW= <'>password<'>;

DATABASE= <'>database-name<'>;

SERVER= <'>server-name<'>;

INTERFACE=<'>file-name<'>;

SYBBUFSZ= |SYBBUF= number-of-rows;

TABLE= <'>table-name<'>;

ASSIGN <=> YES|NO;

DROP <'>column-identifier-1<'> <...<'>column-identifier-n <'>>;

FORMAT <'>column-identifier-1<'> <=>SAS-format-name-1
<...<'>column-identifier-n<'><=> SAS-format-name-n>;

LIST <ALL|VIEW|<'>column-identifier<'>>;

QUIT;

RENAME <'>column-identifier-1<'> <=> SAS-variable-name-1
<...<'>column-identifier-n<'> <=> SAS-variable-name-n>;

RESET ALL|<'>column-identifier-1<'> <...<'>column-identifier-n<'>>;

SELECT ALL|<'>column-identifier-1<'> <...<'>column-identifier-n<'>>;

SUBSET selection-criteria;

UNIQUE <=> YES|NO;

Note: SYBASE is a case-sensitive database. Any DBMS objects that were created with lowercase names or whose names contain special characters must be enclosed in quotation marks. See Case Sensitivity in SYBASE for more information. [cautionend]

DATABASE=<'>database-name<'>;

specifies the name of the SYBASE database that contains the table on which the access descriptor is based. If you omit this statement, the default database for your SYBASE user name is used.

INTERFACE=<'>file-name<'>;

specifies the name and location of the interfaces file that is searched when you connect to the SYBASE server. The interfaces file contains names and access information for the available servers on the network.

If you omit a user name, the default action for your operating system occurs. INTERFACE= is not used in some operating environments. Contact your DBA to see whether this statement applies to your computing environment.

USER= <'>user-name<'>;

specifies the SYBASE user name (also called the login name) that you use when executing the ACCESS procedure. If you omit this statement, your operating system account name is used, if applicable to your operating environment.

PASSWORD=<'>password<'>;

specifies the password for SYBASE. If you omit the password, a default password of NULL is used.

PASSWORD= can also be specified with the PASS= and SYBPW= aliases.

SERVER=<'>server-name<'>;

specifies the server with which to connect. This server accesses the database that contains the table on which a particular access descriptor is based. If the server name contains lowercase or special characters, you must enclose it in quotation marks.

If you omit a server name, the default action for your operating system occurs. On UNIX systems, the value of the environment variable DSQUERY is used if it has been set.

SYBBUFSZ=number-of-rows;

specifies the number of rows of SYBASE data to write to the buffer. If this statement is used, SAS/ACCESS creates a buffer whenever the associated view descriptor is used to browse DBMS data. (Buffering is not performed when the view descriptor is being used to update data.) The interface view engine uses SYBBUFSZ= to improve performance by reducing network traffic.

The specified buffer size can be between 1 and 32,767 rows. If you omit this statement, no data is written to the buffer.

SYBBUFSZ= can also be specified with the SYBBUF= aliases.

ACCESS Procedure Example

In the following example, you create access descriptors and view descriptors for the EMPLOYEES and INVOICE tables. These tables have different owners and are stored in PERSONNEL and INVENTORY databases that reside on different machines. The USER= and PASSWORD= statements identify the owners of the SYBASE tables and their passwords.

SYBASE is a case-sensitive database. The PROC ACCESS database identification statements and the SYBASE column names in all of the statements except SUBSET are converted to uppercase unless the names are enclosed in quotation marks. The SUBSET statements are passed to SYBASE exactly as you type them, so you must use the correct case for the SYBASE column names.

libname vlib 'sas-data-library';

proc access dbms=sybase;
   create work.employee.access;
      server='server1';
      database='personnel';
      user='testuser1';
      password='testpass1';
      table=EMPLOYEES;
   create vlib.emp_acc.view;
      select all;
      format empid 6.;
      subset where DEPT like 'ACC%';
run;

proc access dbms=sybase;
   create work.invoice.access;
      server='server2';
      database='inventory';
      user='testuser2';
      password='testpass2';
      table=INVOICE;
      rename invoicenum=invnum;
      format invoicenum 6. billedon date9. 
        paidon date9.;
   create vlib.sainv.view;
      select all;
      subset where COUNTRY in ('Argentina','Brazil');
run;

options linesize=120;
title 'South American Invoices and 
        Who Submitted Them';

proc sql;
   select invnum, country, billedon, paidon, 
          billedby, lastname, firstnam
      from vlib.emp_acc, vlib.sainv
      where emp_acc.empid=sainv.billedby;

Chapter Contents
Previous
Next
Top of Page