CONNECTION TO Component

CONNECTION TO Component

Retrieves and uses DBMS data in a PROC SQL query or view.

Contains DBMS-specific arguments

Optional component

Syntax
Arguments
Example

Syntax

CONNECTION TO dbms-name | alias (DBMS-query)

The CONNECTION TO component specifies the DBMS connection that you want to use or that you want to create (if you have omitted the CONNECT statement). CONNECTION TO then enables you to retrieve DBMS data directly through a PROC SQL query.

You use the CONNECTION TO component in the FROM clause of a PROC SQL SELECT statement:

PROC SQL;

SELECT column-list

FROM CONNECTION TO dbms-name (DBMS-query)
other optional PROC SQL clauses

QUIT;

CONNECTION TO can be used in any FROM clause, including those in nested queries (that is, subqueries).

You can store a SQL Procedure Pass-Through Facility query in a PROC SQL view and then use that view in SAS programs. When you create a PROC SQL view, any options that you specify in the corresponding CONNECT statement are stored too. Thus, when the PROC SQL view is used in a SAS program, the SAS System can establish the appropriate connection to the DBMS.

On many DBMSs, you can issue a CONNECTION TO component in a PROC SQL SELECT statement directly without first connecting to a DBMS (see CONNECT Statement). If you omit the CONNECT statement, an implicit connection is performed when the first PROC SQL SELECT statement that contains a CONNECTION TO component is passed to the DBMS. Default values are used for all DBMS connection arguments. See your DBMS chapter for details.

Because DBMSs and the SAS System have different naming conventions, some DBMS column names might be changed when you retrieve DBMS data through the CONNECTION TO component. See Long Names and Case Sensitivity in the SQL Procedure and Pass-Through Facility for more information.

Arguments

dbms-name

identifies the database management system to which you direct the DBMS-specific SQL statement. The name for your DBMS is listed in your DBMS chapter.

alias

specifies an alias, if one was defined in the CONNECT statement.

(DBMS-query)

specifies the query that you are sending to the DBMS. The query can use any DBMS-specific SQL statement or syntax that is valid for the DBMS. However, the query cannot contain a semicolon because a semicolon represents the end of a statement in the SAS System.

You must specify a dbms-query argument in the CONNECTION TO component, and the query must be enclosed in parentheses. The query is passed to the DBMS exactly as you type it; therefore, if your DBMS is case sensitive, you must use the correct case for DBMS object names.

On some DBMSs, the dbms-query argument can be a DBMS stored procedure. However, if the stored procedure contains more than one query, only the first query is processed.

Example

The following example sends an ORACLE SQL query, shown in italics, to the ORACLE database for processing. The results from the ORACLE SQL query serve as a virtual table for the PROC SQL FROM clause. In this example, MYCON is a connection alias.

proc sql;
connect to oracle as mycon (user=testuser 
   password=testpass path='myorapath');
%put &sqlxmsg;

select *
   from connection to mycon
    (select empid, lastname, firstname,
               hiredate, salary
          from employees where 
            hiredate>='31-DEC-88');
%put &sqlxmsg;

disconnect from mycon;
quit;

The SAS %PUT macro displays the &SQLXMSG macro variable for error codes and information from the DBMS. See SQL Procedure Pass-Through Facility Return Codes for more information.

The following example gives the query a name and stores it as the PROC SQL view SLIB.HIRES88:

libname slib 'SAS-data-library';

proc sql;
connect to oracle as mycon (user=testuser
password=testpass path='myorapath');
%put &sqlxmsg;

create view slib.hires88 as
   select *
      from connection to mycon
         (select empid, lastname, firstname,
           hiredate, salary
            from employees where 
               hiredate>='31-DEC-88');
%put &sqlxmsg;

disconnect from mycon;

quit;

Chapter Contents
Previous
Next
Top of Page