Summary of Accessing Other Vendors' DBMS Data

Accessing Other Vendors' DBMS Data

Lesson Summary

This page contains

a text summary of the material taught in the lesson
syntax for statements and options
sample programs
points to remember.

I. Text Summary

To go to the page where a task, programming feature, or concept was presented, select a link.

DBMS Access Using the LIBNAME Statement
You can reference DBMS data in your programs by using the SAS/ACCESS LIBNAME statement with the appropriate engine and options. After you associate a libref with a DBMS, its tables and views are accessed like SAS data sets, using conventional two-level SAS names.

Engine Connection Options
SAS/ACCESS engine connection options are passed to the database management system, so their content and format depend on the system. Typical connection options include a user name, password, and database name.

LIBNAME Options
SAS/ACCESS LIBNAME options affect the processing of DBMS objects such as indexes, tables or views. ACCESS=READONLY, for example, prevents writing to the DBMS. When you specify a SAS/ACCESS LIBNAME option, the option is applied to all objects (such as tables and views) in the database that the libref represents.

Preserving Column and Table Names
To preserve spaces, special characters, and case sensitivity in column or table names, specify PRESERVE_COL_NAMES=YES or PRESERVE_TAB_NAMES=YES as SAS/ACCESS LIBNAME options.

DBMS Access Using the Explorer Window
You can associate a libref with a DBMS by selecting File New from the Explorer window. Select Library, and then enter the appropriate settings. The new libref will appear in the Active Libraries window.

Using DBMS Data in SAS Programs
After you associate a libref with a DBMS, you can reference database objects, such as tables and views, by using the syntax libref.object-name. Because DBMS objects are treated as SAS data sets, your SAS programs can process DBMS data by using SAS DATA steps and procedures.

DBMS Access Using the SQL Procedure Pass-Through Facility
It's sometimes more efficient to have a DBMS process the SQL statements. The SQL Procedure Pass-Through Facility allows you to send DBMS-specific statements directly to a database management system for processing.

II. Syntax
To go to the page where a statement or option was presented, select a link.

LIBNAME libref SAS/ACCESS-engine-name
                   <SAS/ACCESS-engine-connection-options>
                   <SAS/ACCESS-LIBNAME-options>;

PROC SQL;

        CONNECT TO dbms-name <AS alias>
                   <(<connect-statement-arguments>
                   <database-connection-arguments>)>;

        SELECT column-list
                FROM CONNECTION TO dbms-name | alias
                             (DBMS-query)

        optional PROC SQL clauses

        EXECUTE (dbms-SQL-statement)
                           BY dbms-name | alias;

        DISCONNECT FROM dbms-name | alias;

QUIT;

III. Sample Programs
libname dbdeux odbc user=cestmoi using=sesame db=receipts connection=globalread dbindex=yes dbconinit='exec update_proc'; proc print data=dbdeux.sales; run; data work.highsale; set dbdeux.sales; if totalsales>500000 then sales="High"; else if totalsales>300000 then sales="Moderate"; else sales="Low"; run; proc print data=work.highsale; run; libname oraclib oracle user=cestmoi password=sesame path=oracpath; preserve_col_names=yes; proc sql dquote=ansi; create table oraclib.gtforty as select lname as LastName, fname as FirstName, salary as Salary format=dollar10.2 from oraclib.staff a, oraclib.payroll b where (a.idnum eq b.idnum) and (salary gt 40000); quit; proc sql; connect to oracle as dblink (user=cestmoi pw=trustme path=orapath connection=global); select * from connection to dblink (select id, lastname, firstname, hiredate, salary from employees
where hiredate>='31-DEC-99'); execute (grant update, insert on orders to cestmoi) by dblink; disconnect from dblink; quit;

IV. Points to Remember

SAS/ACCESS engine connection options are passed directly to your database management system. See your DBMS documentation for required options.

Some SAS/ACCESS LIBNAME options are DBMS-specific. Refer to "SAS/ACCESS Software: DBMS-Specific Information" in SAS/ACCESS Software for Relational Databases for more information.

Don't confuse CONNECT with CONNECTION TO when using the SQL Procedure Pass-Through Facility. The CONNECTION TO component of the Pass-Through Facility is only used in the FROM clause of a PROC SQL SELECT statement.