236 CONNECTION TO Component Chapter 12
CONNECTION TO can be used in any FROM clause, including those in nested
queries (that is, subqueries).
You can store a 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, SAS can establish the appropriate
connection to the DBMS.
On many relational databases, you can issue a CONNECTION TO component in a
PROC SQL SELECT statement directly without first connecting to a DBMS (see
“CONNECT Statement” on page 228). 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 the documentation for your
SAS/ACCESS interface for details.
Because relational databases and SAS have different naming conventions, some
DBMS column names might be changed when you retrieve DBMS data through the
CONNECTION TO component. See Chapter 2, “SAS Names and Support for DBMS
Names,” on page 7 for more information.
Arguments
dbms-name
identifies the database management system to which you direct the DBMS-specific
SQL statement. See the documentation for your SAS/ACCESS interface for the
name for your DBMS.
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 SAS.
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, stored procedures with output parameters are not supported in the
Pass-Through Facility. Further, if the stored procedure contains more than one
query, only the first query is processed.
Example
After you connect (explicitly using the CONNECT statement or implicitly using
default settings) to a DBMS, you can send a DBMS-specific SQL query to the DBMS
using the facilities CONNECTION TO component. You issue a SELECT statement to
indicate which columns you want to retrieve, identify your DBMS (such as Oracle or
DB2), and issue your query using the SQL syntax of your DBMS.
The following example sends an Oracle SQL query, shown by highlighting, to the
Oracle database for processing. The results from the Oracle SQL query serve as a
