CONNECT Statement

Establishes a connection with the data source.

Valid in:

PROC SQL statement

Syntax

Details

Overview

Database Statement Arguments

CONNECT Statement Arguments

Example

Syntax

CONNECT TO data-source-name

CONNECT TO option(s)

Optional Arguments

data-source-name: Specifies the data source to which you want to connect. Because this method requires connecting through a PC Files Server, you must use PCFILES as your data source. You can also specify an optional alias in the CONNECT statement.

alias: specifies an optional alias for the connection that has 1 to 32 characters. If you specify an alias, the keyword AS must appear before the alias. If an alias is not specified, the data source name is used as the name of the pass-through connection.

connect-statement-arguments: specifies arguments that indicate whether you can make multiple connections (shared connections, unique connections, and so on) to the database.

database-connection-arguments: specifies the data source-specific arguments that are needed by PROC SQL to connect to the data source. These arguments are not required. The default behavior opens a dialog box with prompts to specify connection information.

Details

Overview

The CONNECT statement establishes a connection with the data source. You establish a connection to send data source-specific SQL statements to the data source or to retrieve data source data. The connection remains in effect until you issue a DISCONNECT statement or terminate the SQL procedure. See DISCONNECT Statement.

To connect to a data source using the pass-through facility, complete the following steps:

Initiate a PROC SQL step.
Use the pass-through facility CONNECT statement with the PC files engine name and then assign an alias if you want.
Specify any arguments needed to connect to the database.
Specify any attributes for the connection.

The CONNECT statement is optional for some data sources. However, if you do not specify it, default values for all database connection arguments are used.

Any return code or message that is generated by the data source is available in the macro variables SQLXRC and SQLXMSG after the statement executes. See Return Codes for more information about these macro variables.

Database Statement Arguments

The arguments that are listed below are available with the pass-through facility for PC files. These arguments provide information to the pass-through facility to connect to the PC files or to the database. These options are used when connecting to PC Files Server.

DSN= data-source-name: specifies the ODBC data source name that is used to access the PC data through an ODBC driver on the PC.

Note:This ODBC data source must be defined on the PC where the PC Files Server is currently running.

CONNECT_STRING= connection-string: specifies connection options for your data source or database. Separate multiple options with semicolons. This is an advanced connection method that you should use only when you know the exact syntax of all connection options that the ODBC driver requires for a successful connection.

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

PORT= port-number: The port or service name on the PC that the SAS PC Files Server is listening on. This port or service name is displayed on the SAS PC Files Server window when it is started on the PC. This is a required field when connecting to the PC Files Server for data.

Alias:SERVICE | SERVICE_NAME

Default:9621

SERVER= pc-server-host-name: specifies the computer name of the PC on which you started the PC Files Server. This name is required by UNIX users to connect to this server machine and is reflected on the server control panel. This is a required field when connecting to the PC Files Server for data.

You can specify this host name as a simple computer name (for example, wxp320), a fully qualified network name (for example, wxp320.domain.com), or an IP address.

Note: Omitting the SERVER= option on Microsoft Windows clients invokes Autostart.

SERVERUSER= 'domain\server-user-name': specifies the domain name and User ID for the PC running PC Files Server. Always enclose the value in quotation marks. Otherwise, the backslash can be misinterpreted by the SAS parser.

Alias:SERVERUID

Notes:If you are not on a domain, omit the domain name and the backslash.

Use the USER= option for database user IDs.

SERVERPASS= 'server-user-password': specifies the password for the PC Files Server for the user ID given. If the account has no password, omit this option. Always enclose the value in quotes in order to preserve the case of the password.

Alias:SERVERPASSWORD | SERVERPW | SERVERPWD

Notes:Passwords are generally case sensitive.

Use the PASSWORD= option for database passwords.

SSPI= YES | NO: enables the PC Files Server to allow Integrated Windows Authentication. This is a mechanism for the Windows client and server to exchange credentials.

Default:NO

Restriction:Microsoft Windows 64-Bit only.

Note:SSPI can also be enabled by specifying the –SSPI option on the SAS command line.

DBPASSWORD= database-password: enables you to access your file if you have database-level security set in your MDB file. A database password is case sensitive, and you can define it instead of user-level security.

Restriction:Microsoft Access only.

DBSYSFILE= workgroup-information-file: contains information about the users in a workgroup based on information that you define for your Microsoft Access database. Any user and group accounts or passwords that you create are saved in the new workgroup information file.

PASSWORD= user-password: specifies a password for the user account, if required by the data source. Passwords are case sensitive.

MSENGINE= ACE | JET: determines the database engine used for accessing the Microsoft Excel file or Microsoft Access database. The Microsoft Jet engine is older and supports formats up to 2003. The Microsoft ACE engine supports Microsoft Excel 2007 and Microsoft Access 2007 and older formats.

Default:ACE

USER= User ID: specifies a default user account name. The default value is Admin. User names can be 1 to 20 characters long and can include alphabetic characters, accented characters, numbers, and spaces. If you have user-level security set in your MDB file, you need to use this option and the PASSWORD= option to access your file.

VERSION= 2007 | 2003 | 2002 | 2000 | 97 | 95 | 5: sets the version of Microsoft Excel workbook. The default value is 97.

Alias:VER

Restriction:Microsoft Excel only.

Note:You do not need to specify this option for an existing Microsoft Excel file. If you want to create a new Microsoft Excel workbook file, you can use this option to specify the version that you want to create. Note that versions 97, 2000, and 2003 of Excel share the same file format. Versions 95 and 5 share a separate file format.

CONNECT Statement Arguments

Connect Statement arguments are supported by the pass-through facility CONNECT statement for PC Files. These arguments extend some of the LIBNAME statement connection management features to the pass-through facility.

AUTOCOMMIT= YES | NO

determines whether the ACCESS engine commits (saves) updates as soon as they are submitted.

YES: specifies that updates are committed (saved) to the table as soon as they are submitted. No rollback is possible.

NO: specifies that the SAS/ACCESS engine automatically performs the commit when it reaches the end of the file.

Default:YES

Note:The default value for this option is different from the LIBNAME option.

COMMAND_TIMEOUT= number-of-seconds: specifies the number of seconds before a data source command times out.

Alias:TIMEOUT

Default:0 (no time-out)

CONNECTION=SHARED | GLOBAL

specifies whether multiple CONNECT statements for a data source can use the same connection. The CONNECTION= option enables you to control the number of connections, and therefore transactions, that your SAS/ACCESS engine executes and supports for each CONNECT statement.

SHARED: specifies that the CONNECT statement makes one connection to the DBMS. Only pass-through statements that use this alias share the connection.

GLOBAL

specifies that multiple CONNECT statements can share the same connection to the DBMS.

The CONNECT statements must use identical values for the CONNECTION= option.
The CONNECT statement must use identical values for the CONNECTION_GROUP= option.
Database connection arguments must be identical.

Default: SHARED

CONNECTION_GROUP= connection-group: causes operations against multiple librefs to share a connection to the data source. Also causes operations against multiple pass-through facility CONNECT statements to share a connection to the data source.

CURSOR_TYPE= DYNAMIC | FORWARD_ONLY | KEYSET_DRIVEN | STATIC

specifies the cursor type for read-only cursors and for cursors to be updated.

DYNAMIC: specifies that the cursor reflects all changes that are made to the rows in a result set as you move the cursor. The data values and the membership of rows in the cursor can change dynamically on each fetch. This is the default for the DB2 UNIX, PC files, and Microsoft SQL Server interfaces.

FORWARD_ONLY: specifies that the cursor behaves like a DYNAMIC cursor, except that it supports only fetching the rows sequentially.

KEYSET_DRIVEN: specifies that the cursor determines which rows belong to the result set when the cursor is opened. However, changes that are made to these rows are reflected as you scroll around the cursor.

STATIC: specifies that the complete result set is built when the cursor is opened. No changes that are made to the rows in the result set after the cursor is opened are reflected in the cursor. Static cursors are read-only.

Alias:CURSOR

Default:None

DBGEN_NAME= DBMS | SAS

specifies that the data source columns are renamed, and specifies the format that the new names follow.

DBMS: specifies that the data source columns are renamed to valid SAS variable names. Disallowed characters are converted to underscores. If a column is converted to a name that already exists, then a sequence number is appended to the end of the new name.

SAS: specifies that data source columns are renamed to the format _COLn, where n is the column number (starting with zero).

Default:DBMS

DBMAX_TEXT= n: specifies an integer between 1 and 32,767 that indicates the maximum length for a character string. Longer character strings are truncated. This option applies only when you are reading, appending, and updating Microsoft Access or Excel character data from SAS.

Default:1,024

Note:Although you can specify a value less than 256, it is not recommended.

DEFER= NO | YES

enables you to specify when the CONNECT statement occurs.

NO: specifies that the connection to the data source occurs when the libref is assigned by a LIBNAME statement.

YES: specifies that the connection to the data source occurs when a table in the data source is opened.

Default:NO

READBUFF= number-of-rows: specifies the number of rows to use when you are reading data from a data source. Setting a higher value for this option reduces I/O and increases performance, but also increases memory usage. In addition, if too many rows are read at once, values returned to SAS might be out of date.

Aliases:ROWSET=

ROWSET_SIZE=

Default:1

STRINGDATES= YES | NO

specifies whether datetime values are read from the data source as character strings or as numeric date values. STRINGDATES= is not available as a data set option.

YES: specifies that SAS/ACCESS reads datetime values as character strings.

NO: specifies that SAS/ACCESS reads datetime values as numeric date values.

Alias: STRDATES

Default: NO

USEDATE= YES | NO

specifies whether to assign the DATE. format or the DATETIME. format for datetime columns in the data source table while importing data from a Microsoft Access database or a Microsoft Excel workbook.

YES: specifies that the DATE. format is assigned to datetime columns in the data source table.

NO: specifies that the DATETIME. format is assigned for datetime columns in the data source table.

Alias: USE_DATE | USE_DATETYPE

Default: NO

Example

The following example uses the CONNECT statement with the PATH= option to connect to the Microsoft Access database file, c:\demo.mdb:

PROC SQL;
CONNECT TO PCFILES AS db (SERVER=d2323 PATH=' c:\demo.mdb' );