LIBNAME Statement and Pass-Through Facility for PC Files on Microsoft Windows

CONNECT Statement

Establishes a connection with the data source.

Valid in:

PROC SQL statement

Syntax
Arguments
DATABASE Connection Arguments
CONNECT Statement Arguments
Details
Example

Syntax

CONNECT TO data-source-name <AS alias> <(<connect-statement-arguments> <database-connection-arguments>)>;

Arguments

data-source-name

identifies the data source to which you want to connect, such as SAS/ACCESS for Microsoft Access or EXCEL for Microsoft Excel. 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 or unique, or other connections to the database. These CONNECT Statement Arguments are optional.

See:	CONNECT Statement Arguments.

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, and the default behavior opens a dialog box.

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.

PATH='file-path'

specifies the path of the Microsoft Access database or the Microsoft Excel workbook file.

determines whether user is prompted for connection information to the data source.

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);

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

REQUIRED

connect with a valid data-source file 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 prompting for connection information for the data source.

UDL

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

Note:

This statement also applies to the INIT= option and the UDL= option.

UDL=path-for-udl-file

specifies the path and filename for a UDL (a Microsoft data link file ), as in this example:

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

This option does not support SAS filerefs. The macro variable SYSDBMSG is set upon successful completion. For more information, see Microsoft documentation on the data link API.

Alias:	UDL_FILE
Note:	This option should not be used with a physical filename or other connection options, such as PATH and INIT.

CONNECT Statement Arguments

Valid CONNECT statement arguments for use with the Pass-Through Facility. These arguments extend some of the LIBNAME statement connection management features to the Pass-Through Facility.

AUTOCOMMIT=YES | NO

determines whether the SAS/ACCESS engine commits (saves) updates as soon as you submit them.

YES: specifies that updates are committed (that is, saved) to table as soon as they are submitted, and 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: Different from the LIBNAME option default. [cautionend]

COMMAND_TIMEOUT=number-of-seconds

specifies the number of seconds that pass before a data source command times out.

Default:	0 (no time-out)
Alias:	TIMEOUT

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 if they use identical values for CONNECTION=, CONNECTION_GROUP=, and any database connection arguments.

Default:

SHARED

CONNECTION_GROUP= connection-group

groups operations against multiple librefs to share a connection to the data source. Also groups operations against multiple Pass-Through Facility CONNECT statements to share a connection to the data source.

CURSOR_TYPE=KEYSET_DRIVEN | STATIC

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

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 move the cursor. The OLE DB property DBPROP_OTHERUPDATEDELETE=TRUE for keyset cursors and FALSE for static cursors.
STATIC: specifies that the complete result set is built when the cursor is opened. Changes made to the result set are not reflected in the cursor. Static cursors are read-only.

Default:

None

Alias:

CURSOR

DBENCODING=12-byte SAS encoding-value

indicates the encoding used to save data in DBF files. Encoding maps each character in a character set to a unique numeric representation, which results in a table of code points. A single character can have different numeric representations in different encodings.

For example, some DBF files were saved with pcoem850 encoding. When you are importing these DBF files in Microsoft Windows, specify:

:DBENCODING=pcoem850.

The IMPORT procedure reads and transcodes data from pcoem850 to Microsoft Windows default WLATIN1.

Note:

Refer to the SAS NLS User's Guide for information about transcoding and valid encoding values.

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.

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

Default:

1,024

DEFER=NO | YES

enables you to specify when the connection to the data source 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:

MSENGINE= ACE|JET

determines the database engine used for accessing the Microsoft Excel file or the Microsoft Access database. The Microsoft Jet engine is older and supports formats up to 2003. The Microsoft Ace engine supports 2007 and older formats.

Restriction:	It is recommended that you do not use this file option unless you are trying to create a 95 format file.
Default:	ACE

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. Additionally, if too many rows are read at once, values returned to SAS might be out of date.

Default:	1
Alias:	ROWSET= ROWSET_SIZE=

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 date time values as numeric date values.

Default:	NO
Alias:	STRDATES

USE_DATETYPE=YES | NO

specifies whether to use DATE. format for date and time columns or fields in the data source table while importing data from Microsoft Access database or Excel workbook.

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

Default:	NO
Alias:	STRDATES

Details

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

Follow these steps to connect to a data source using the Pass-Through Facility:

Initiate a PROC SQL step.
Use the CONNECT statement with the Pass-Through Facility, identify the data source (such as Microsoft Access or Excel), and 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 the data source generates is available in the macro variables SQLXRC and SQLXMSG after the statement executes.

See Return Codes

Example

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

PROC SQL;
CONNECT TO ACCESS AS DB (PATH='c:\demo.mdb');

Top of Page