CONNECTION TO Component

CONNECTION TO Component

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

Optional component

Syntax
Arguments
Syntax

Syntax

CONNECTION TO OLEDB | alias (DBMS-SQL-query)

Arguments

alias

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

(DBMS-SQL-query)

specifies the query that you are sending to the data source, such as a relational DBMS. Data sources other that relational databases can be specified in the SQL Procedure Pass-Through Facility, but they must have SQL capabilities. 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 to the SAS System, a semicolon represents the end of a statement.

You must specify a DBMS-SQL-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 either use the correct case for names or you must quote them. Quoted character strings are limited to 32Kb characters. Or for some DBMSs, the DBMS-SQL-query argument can be a DBMS-specific SQL EXECUTE statement that executes a DBMS stored procedure. However, if the stored procedure contains more than one query, only the first query is processed.

The CONNECTION TO component specifies the data source connection that you want to use or that you want to establish (if you have omitted the CONNECT statement). CONNECTION TO then enables you to retrieve data from the data source 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-SQL-query) other-optional-PROC-SQL-clauses;

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

You can store a Pass-Through 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 data source.

On many DBMSs, you can issue a CONNECTION TO component in a PROC SQL SELECT statement directly without first connecting to a DBMS . 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 connection arguments.

Because DBMSs and the SAS System have different naming conventions, some DBMS column names might be truncated when you retrieve DBMS data through the CONNECTION TO component. Default SAS variable names follow these rules:

If the column name is longer than thirty-two characters, the SAS System uses only the first thirty-two characters. If truncating results in duplicate names, sequential numbers (starting with zero) are appended to the ends of the names.
If the column name contains characters that are invalid SAS names (such as national characters), the SAS System replaces the invalid characters with underscores (_). For example, the column name func$ becomes the SAS variable name func_.

Chapter Contents
Previous
Next
Top of Page