CONNECTION TO Component
Retrieves and uses data source data in a PROC SQL
query or view.
Valid in: |
SQL procedure STEP statements. |
Syntax
CONNECT TO a data- source AS alias (connect
statement arguments)
(database connection-options)
Summary of Optional Arguments
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.
- PROMPT= YES | NO | REQUIRED | NOPROMPT
| PROMPT | UDL
-
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`);