CONNECTION TO Component

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

Valid in:

SQL procedure STEP statements.

Syntax

Details

Example

Syntax

CONNECT TO a data- source AS alias (connect statement arguments)
(database connection-options)

Summary of Optional Arguments

ALIAS

DATABASE CONNECTION ARGUMENTS

DATA SOURCE NAME

CONNECTION COMPONENT

Optional Arguments

ALIAS: specifies the data source alias for the connection. If you specify an alias, the keyword AS must appear before the alias.

Restriction: ALIAS is not supported if the CONNECT statement is omitted.

Requirement: The range of the ALIAS is between 1 and 32 characters.

Note:The data source name is used as the name of the pass-through connection if an alias is not specified.

CONNECTION COMPONENT: specifies arguments that indicate whether you can make multiple connections, shared connections, or unique connections, to the database.

DATA SOURCE NAME: specifies the data source name to which you want to connect and direct the data source-specific SQL statements.

Requirement: You must use back quotation marks ( ` ), not single (forward) quotation marks, to enclose any data source name that contains a space.

Note:The data source name becomes the name of the pass-through connection if an alias is not specified.

DATABASE CONNECTION ARGUMENTS: specifies the data source-specific arguments to the pass-through facility that are needed by the SQL procedure to connect to a data source.

Database Connection Arguments

Connection arguments provide database connection information to the pass-through facility to connect to a Microsoft Access database or a Microsoft Excel workbook file.

INIT= "initialization-string": specifies the initialization string when connecting to a data source.

Note:This statement option applies to the INIT= option and the UDL= option.

MSENGINE= ACE | JET: determines the database engine to use for accessing Microsoft Excel files or Microsoft Access databases. The Microsoft Jet engine supports Microsoft formats up to 2003. The Microsoft Ace engine supports 2007 formats and formats in subsequent releases of Windows.

Default: ACE

Restriction: It is recommended that this option is used to create only a Windows 95 format file.

PATH= data-source-path: specifies the path of the Microsoft Access database or the Microsoft Excel workbook file.

specifies whether user is prompted for data source connection information.

YES enables prompting with a Data Link Properties dialog box. To write the initialization string to the SAS log, submit this code immediately after connecting to the data source:

%PUT %SUPERQ (SYSDBMSG);

NO prompting is not available. You must specify the data source as a physical filename or complete path.

REQUIRED connect with a valid data-source-name. If a valid connection is not specified, you are prompted for the connection options. The prompt enables you to change the data source file and other properties.

NOPROMPT disables the display of the Data Link Properties window. Prompting is not available.

PROMPT enables the display of the Data Link Properties window. Prompting is available.

UDL= enables you to browse and select an existing Microsoft data link file (.udl).

Note: This statement option applies to the INIT= argument and the UDL= argument.

UDL= "path and filename"

specifies the path and filename for a UDL (a Microsoft data link file ). This option does not support SAS filerefs. The macro variable SYSDBMSG is set upon successful completion.

UDL_FILE='C:\WinNT\profiles\me\desktop\MyDBLink.udl'; 
          %PUT %SUPERQ(SYSDBMSG);

Alias:UDL_FILE

See:Microsoft Data Link API documentation.

Additional Options for Microsoft Access Database Only

DBPASSWORD= database-file-password: enables you to access database files with database-level security. This security level can be defined instead of user-level security.

Alias: DBPWD | DBPW | PASS | PASSWORD

Restriction: Microsoft Access Database only.

Note: Database password is case sensitive.

DBSYSFILE= workgroup-information-file: specifies the workgroup information file. This file contains a collection of information defined for the Microsoft Access database. User, group accounts, and passwords that you create are saved in the workgroup information file.

Alias: SYSTEMDB

Restriction: Microsoft Access database files only.

PASSWORD= user-password: specifies a password required by the data source for the user account.

Alias: PWD | PW | PASS | PASSWORD

Note: Passwords are case sensitive.

USER= user-id: specifies a user account name, if one is required to connect to the data source. For Microsoft Access, if you have user-level security set on your .mdb file, you need to use the USER= and PASSWORD= options to access your file.

Alias: UID

Restriction: Microsoft Access database files only.

Note: Use the SERVERUSER= option to connect to a server.

Details

The CONNECTION component specifies the data source connection to use or to create. CONNECTION enables you to retrieve data source data directly through an SQL procedure query.

The CONNECTION component can be used in any FROM clause, including those in nested queries (subqueries).
You can store a pass-through facility query in an SQL view and then use that view in SAS programs.
When you create an SQL view, any options that you specify in the corresponding CONNECTION statement are stored too. Thus, when the SQL view is used in a SAS program, SAS can establish the appropriate connection to the data source.
Because external data sources and SAS have different naming conventions, some data source column names might be changed when you retrieve data source data through the CONNECTION component.

Example: Connect and Query a Table

Use the CONNECTION component to query a table or a subtable after the connection:

SELECT * FROM CONNECTION TO db(SELECT * FROM `my invoice`);
SELECT * FROM CONNECTION TO db
(SELECT `Invoice Number`, Amount from `my invoice`);