CONNECT Statement

CONNECT Statement

Establishes a connection with the DBMS

Syntax
Arguments
ODBC Connection Arguments
CONNECT Examples
Tips for Connecting to a Microsoft Excel Data Source

Syntax

CONNECT TO ODBC <AS alias > <(ODBC-connection-arguments)>;

Arguments

You use the following arguments with the CONNECT statement:

alias: specifies an optional alias that has 1 to 32 characters. If you specify an alias, the keyword AS must appear before the alias.
(ODBC-connection-arguments): specifies the DBMS-specific arguments that are needed by PROC SQL in order to connect to the DBMS. These arguments must be enclosed in parentheses. For some databases, these arguments have default values and therefore are optional. The arguments for ODBC are described in the following sections.

ODBC Connection Arguments

PROC SQL supports multiple connections to ODBC. If you use multiple simultaneous connections, you must use the alias argument to identify the different connections. If you do not specify an alias, the default alias, odbc, is used. The functionality of multiple connections to the same ODBC data source may be limited by the particular data source's driver.

The CONNECT statement is required when connecting to ODBC data sources by way of the SQL Pass-Through Facility.

The following list describes the arguments that are used for ODBC in the CONNECT statement. You can use the arguments DSN=, UID=, and PWD= to connect to most data sources. Use the PROMPT, NOPROMPT, COMPLETE, REQUIRED, or AUTOCOMMIT arguments to provide additional information or to select and connect to the data source.

The arguments for the DBMS connection information arguments can be quoted by using either single or double quotes. Some values may include embedded spaces, semicolons, or quotes and, therefore, must be quoted.

Note: Not all of these engine connection options are supported by all ODBC drivers. Refer to your vendor-supplied documentation for more information. [cautionend]

There are multiple ways that you can connect to the DBMS when using the CONNECT statement. Use only one of the following methods for each connection since they are mutually exclusive:

specify USER=, PASSWORD=, and DATABASE=, or
specify COMPLETE=, or
specify NOPROMPT=, or
specify PROMPT=, or
specify REQUIRED=.

AUTOCOMMIT=YES | NO

indicates whether or not updates are committed immediately after they are submitted.

If AUTOCOMMIT=YES, no rollback is possible.

If AUTOCOMMIT=NO, the SAS/ACCESS engine automatically does the commit when it reaches the end of the file.

The default value for AUTOCOMMIT= under ODBC is NO if the ODBC driver supports transactions and the connection is used for updating. Otherwise, the default value is YES. The default value is always YES when the PROC SQL Pass-Through facility is used.

DSN | DS | DATASRC | DATABASE=<'>data-source-name<'>

specifies the ODBC data source to which you want to connect. For PC platforms, data sources must be configured by using the ODBC icon in the Windows Control Panel. For UNIX platforms, data sources must be configured by modifying the .odbc.ini file. DSN= indicates that the connection is attempted using the ODBC SQLConnect API, which requires a data source name. Optionally, a user ID and password (described below) can be used in conjunction with DSN=. This API is guaranteed to be present in all drivers.

Specify either DSN= or one (and only one) of the following arguments: PROMPT, NOPROMPT, COMPLETE, or REQUIRED. These arguments are all mutually exclusive of each other.

UID | USER=<'>DBMS-user-id<'>

specifies the DBMS user ID. The UID= argument can be used only in conjunction with the DSN= argument. Not all ODBC drivers accept user IDs. This argument is optional.

PWD | PASSWORD | PW | PASS | USING=<'>DBMS-password<'>

specifies the DBMS password. The PWD= argument can be used only in conjunction with the DSN= argument. Not all ODBC drivers accept passwords. This argument is optional.

COMPLETE=<'>connection-options<'>

specifies connection options for your data source or database. If you specify enough correct connection options, the SAS/ACCESS engine connects to your data source or database. Otherwise, you are prompted for the connection options with a dialog box. Separate multiple options with a semicolon. When a successful connection is made, the complete connect string is returned in the SYSDBMSG and SQLXMSG macro variables.

COMPLETE= is similar to the PROMPT= option. However, if COMPLETE= attempts to connect and fails, then a dialog box is displayed and you can edit values or enter additional values.

COMPLETE= is optional.

See your driver documentation for more details.

NOPROMPT=<'>connection-options<'>

specifies connection options for your data source or database. You separate multiple options with a semicolon. If you specify enough correct connection options, the SAS/ACCESS engine connects to the data source or database. Otherwise, an error is returned and no dialog box is displayed. NOPROMPT= is optional. If connection options are not specified, the default settings are used.

PROMPT=<'> connection-information<'>

specifies connection options to the data source.

A dialog box is displayed, using the values from the PROMPT= connection string. You can edit any field before you connect to the data source. When a successful connection is made, the complete connect string is returned in the SYSDBMSG macro variable.

PROMPT= is similar to the COMPLETE= option. However, unlike COMPLETE=, PROMPT= does not attempt to connect to the DBMS first. It displays the dialog box where you can edit or enter additional values.

REQUIRED=<'>connection-options<'>

specifies connection options for your data source or database. You separate multiple options with a semicolon.

If you specify enough correct connection options, such as user ID, password, and data source name, the SAS/ACCESS engine connects to the data source or database. Otherwise, a dialog box is displayed to prompt you for the connection options. Options in the dialog box that are not related to the connection are disabled. REQUIRED= only allows you to modify required fields in the dialog box. When a successful connection is made, the complete connect string is returned in the SYSDBMSG macro variable.

REQUIRED= is similar to COMPLETE= because it attempts to connect to the DBMS first. However, if REQUIRED= attempts to connect and fails, then a dialog box is displayed and you can only edit values that are in the required fields.

REQUIRED= is optional.

CONNECT Examples

These examples use ODBC to connect to a data source that is configured under the data source name User's Data using the alias USER1. The first example uses the connect method that is guaranteed to be present at the lowest level of ODBC conformance. Note that DSN= names may contain quotes and spaces.

proc sql;
   connect to ODBC as user1
   (dsn="User's Data" uid=testuser pwd=testpass);

The next example uses the connect method that represents a more advanced level of ODBC conformance. It uses the input dialog box that is provided by the driver. The DSN= and UID= arguments are within the connect string and, therefore, are not parsed by the Pass-Through facility but instead are passed to the ODBC manager.

proc sql;
   connect to odbc as user1
   (required = "dsn=User's Data;uid=testuser");

The next ODBC example enables you to select any data source that is configured on your machine. The example uses the connect method that represents a more advanced level of ODBC conformance, Level 1. When a successful connection is made, the connect string is returned in the SQLXMSG and SYSDBMSG macro variables and can be stored if this method is used to configure a connection for later use.

proc sql;
   connect to odbc (required);

This last ODBC example prompts you to specify the information that is required to make a connection to the DBMS. You are prompted to supply the data source name, user ID, and password in the dialog boxes that are displayed.

proc sql;
   connect to odbc (prompt);

Tips for Connecting to a Microsoft Excel Data Source

To connect to a Microsoft Excel (5.0, 7.0, and 8.0) data source, you must define a database as a named range within Excel. For this example, NEWSALES.XLS is the Excel file that contains sales data. Cell A1 contains "Region" and cell D5 contains the numeric value "399". To create a named range within Excel, select the entire range including all column names. For this example, you would highlight the entire range between cells A1 and D5. From the Excel menu items, select Insert, select Name, and select Define. Type the name of the range, for example, SALES_Q1. To work with this data in SAS, you must be sure to save the .XLS file and close the file within Excel to prevent file locking errors when SAS uses ODBC to access the Excel file.

Multiple named ranges can exist within an Excel file. Each one would be treated as a separate table.

You must also create a data source name within the ODBC Administrator on your PC. Within the ODBC Administrator, select the Microsoft Excel ODBC driver and create a DSN. For this example, the DSN is named SALES_97 and is based on the Excel file NEWSALES.XLS. The following example will create a view and print the contents of the named range, SALES_Q1, from the Excel file NEWSALES.XLS.

proc sql;
   connect to odbc as mydb (dsn=sales_97);

create view regsales as
  select * from connection to mydb 
    (select * from sales_q1);
quit;

proc print data=regsales;
run;

Chapter Contents
Previous
Next
Top of Page