LIBNAME Statement: PC Files on Microsoft Windows

LIBNAME Options

The LIBNAME statement options provide additional control over the way that SAS processes PC files data. For many tasks that you do not need to specify any of these advanced options.

Many of these options are also available as data set options.

ACCESS=READONLY

indicates that tables and views can be read but not updated.

AUTOCOMMIT=YES | NO

specifies whether the ACCESS engine commits updates when submitted.

Default:	NO
See:	Data Set Options

YES: specifies that updates are committed to a table as soon as they are submitted. No rollback is possible.
NO: specifies that updates are committed when SAS reaches the end of the file.

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= SHAREDREAD | UNIQUE | GLOBALREAD

specifies whether operations against a single libref share a connection to the data source. Also specifies whether operations against multiple librefs share a connection to the data source.

SHAREDREAD

specifies that all READ operations that access data source tables in a single libref share a single connection. A separate connection is established for each table that is opened for update or output operations.

Where available, this is usually the default value because it offers the best performance and it guarantees data integrity.

UNIQUE

specifies that a separate connection is established every time a data source table is accessed by a SAS application.

GLOBALREAD

specifies that all READ operations that access data source tables with multiple librefs, share a single connection if these conditions are met:

the librefs are created by LIBNAME statements that specify:
- identical values for the CONNECTION= option.
- identical values for the CONNECTION_GROUP= option.
- identical values for all data source connection options.

A separate connection is established for each table that is opened for update or output operations.

Default:	SHAREDREAD
See:	CONNECTION_GROUP

CONNECTION_GROUP

specifies that operations against multiple librefs share a single connection to the data source. Also specifies that operations against multiple pass-through facility CONNECT statements share a single connection to the data source.

CURSOR_TYPE=KEYSET_DRIVEN | STATIC

specifies the cursor type for read-only cursors and for cursors to be updated. If you do not set CURSOR_TYPE= , the Jet provider that you are using determines the default.

KEYSET_DRIVEN: specifies that the cursor determines which rows belong to the result set when you open the cursor. Changes that are made to these rows are reflected as you move the cursor. The OLE DB property DBPROP_OTHERUPDATEDELETE=TRUE for key set driven cursors.
STATIC: specifies that the complete result set is built when you open the cursor. No changes that are made to the result set are reflected in the cursor. Static cursors are read-only. The OLE DB property DBPROP_OTHERUPDATEDELETE=FALSE for static cursors.

Alias:

CURSOR

DBCOMMIT=number-of-rows

affects update, delete, and insert processing. The number of rows that are processed includes rows that are not processed successfully. If you set DBCOMMIT= 0, a commit is issued only once (after the procedure or DATA step completes). If the DBCOMMIT= option is explicitly set, SAS/ACCESS fails any update that has a WHERE clause.

Default:	1,000 [inserting] 0 [updating; commit occurs when data set or procedure completes]
Note:	If you specify both DBCOMMIT= and ERRLIMIT= options, and these options collide during processing, DBCOMMIT= is issued first and ERRLIMIT= is issued second. Because the DBCOMMIT= option is issued before the ERRLIMIT= option, the DBCOMMIT= option overrides the ERRLIMIT= option in this situation.

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 are 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 the format used for the names.

DBMS

specifies that the data source columns are:

renamed to valid SAS variable names.
invalid characters are converted to underscores.
if a column-name is converted to an existing name, then a sequence number is appended to the new name.

SAS

specifies that data source columns are renamed to the format _COLn, where n is the column number. Zero-based, starts at zero.

Default:

DBMS

DBMAX_TEXT=integer between 1 and 32,767

specifies the maximum length for a character string. Character strings longer than 32,767 are truncated. This option only applies when you are reading, appending, and updating character data in a Microsoft Access database or Microsoft Excel workbook from SAS.

Default:	1,024
Note:	Although you can specify a value less than 256, it is not recommended for reading data from a Microsoft Access database.

DBNULLKEYS=YES|NO

specifies whether there might be NULL values in the columns.

YES

if there might be null values in the transaction table or the master table
for the columns that you specify in the DBKEY= option use DBNULLKEYS=YES.
When you specify DBNULLKEYS=YES and specify a column that is not defined
as NOT Null in the DBKEY= data set option, SAS generates WHERE clause
that can find NULL values. For example if you specify DBKEY=Column and
COLUMN is not defined as NOT NULL SAS generates a WHERE clause with
this syntax:
```
WHERE ((COLUMN = ?) or ((COLUMN IS NULL) AND  (? IS NULL))) 
```
This syntax enable SAS to prepare the statement once and use it for any (NULL or NOT NULL in the column.

Note: This syntax has the potential to be much less efficient than the shorter form the WHERE clause presented below.

In the DBKEY= option, and there might be NULL values in a column, specify DBNULLKEYS=YES. SAS generates a WHERE clause to find NULL values.
If you specify DBNULLKEYS=YES and a column not defined NOT NULLDBKEY= data set option, SAS generates a WHERE clause to find NULL values.
If you specify DBKEY=COLUMN and COLUMN is not defined as NOT NULL, SAS generates a WHERE clause.

Example of a WHERE clause:

WHERE ((COLUMN = ?) OR ((COLUMN IS NULL) AND (? IS NULL)));

NO

If you specify DBNULLKEYS=NO or specify a column that is defined as NOT NULL in the DBKEY= option, SAS generates a simple WHERE clause.
If you know that there are no NULL values in the transaction or the master table for the columns specified in the DBKEY= option, use DBNULLKEYS=NO.
If you specify DBNULLKEYS=NO and specify DBKEY=COLUMN, SAS generates a shorter form of a WHERE clause. The WHERE clause is generated whether the column that DBKEY= is defined as NOT NULL.

Example WHERE (COLUMN = ?)

Note: This syntax enables SAS to prepare the statement once and use it for any value, NULL, or NOT NULL in the column. [cautionend]

Default:

YES

DBSASLABEL=COMPAT|NONE

specifies whether SAS/ACCESS saves the data source column names as SAS label names. This option is valid only when reading data into SAS from the data source.

COMPAT: specifies that the data source column names are saved as SAS label names. This is compatible to the previous SAS releases.
NONE: specifies that the data source column names are not saved as SAS label names. SAS label names are left as null values.

Default:

COMPAT

DEFER=NO|YES

specifies when a 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:

DIRECT_SQL=YES|NO|NONE specific-functionality

specifies whether generated SQL is passed to the data source for processing.

YES

specifies that whenever possible, generated SQL, except multiple outer joins, is passed to the data source for processing. This includes SQL that is generated from PROC SQL, SAS functions that can be converted into data source functions, joins, and WHERE clauses.

NO

specifies that generated SQL from PROC SQL is not passed to the data source for processing. This is the same as specifying the specific-functionality value NOGENSQL.

NONE

specifies that generated SQL is not passed to the data source for processing. This includes SQL that is generated from PROC SQL, SAS functions that can be converted into data source functions, joins, and WHERE clauses.

specific-functionality

identifies types of processing to be handled by SAS instead of the data source. Specify any of these values:

NOFUNCTIONS

causes SAS to handle all SAS functions. The SAS functions are not converted into data source functions and are not passed to the data source for processing.

NOMULTOUTJOINS

causes SAS to process outer joins that involve more than two tables.

Note:	this option is always set for the Microsoft Jet engine.
Restriction:	this option does not affect outer joins of two tables.

NOGENSQL

prevents PROC SQL from generating SQL to be passed to the data source for processing.

NOWHERE

prevents WHERE clauses from being passed to the data source for processing. This includes SAS WHERE clauses and PROC SQL generated or PROC SQL specified WHERE clauses.

Default:

YES

FILELOCK= YES|NO

specifies the access level to a Microsoft Excel file. By default, FILELOCK is not set. Specifying FILELOCK=YES indicates that the LIBNAME engine checks and denies a connection if the file was opened by Excel or another application.

CAUTION:: SAS does not check whether the Excel file was opened by Excel or another application when you assign a LIBNAME statement for the file. A potential access violation can occur if a user attempts to update the Excel file using Microsoft Excel.

YES: allows only one LIBNAME assignment with READ and WRITE permission to connect to the file. The LIBNAME assignment is denied if the file has been opened by Excel or any other application.
NO: specifies that the Microsoft Excel LIBNAME engine behaves the same as in SAS 9.1.

Restriction:

applies only to the Microsoft Excel LIBNAME engine; has no effect on the Microsoft Access LIBNAME engine.

allows other applications to open the Excel file with READONLY permission to connect to the file. The LIBNAME statement is: READONLY LIBNAME, set FILELOCK=YES with ACCESS=READONLY. This allows other applications to open the Excel file in READONLY mode with connection information set to browse data.

INSERT_SQL= YES|NO

specifies the method that is used to insert rows into a data source.

YES	specifies that the SAS/ACCESS engine uses the data source's SQL insert method to insert rows into a table.
NO	specifies that the SAS/ACCESS engine uses an alternate (data source-specific) method to add rows to a table.

Default:

INSERTBUFF=number-of-rows

specifies the number of rows for a multi-row insert. If the INSERTBUFF value is greater than the DBCOMMIT value, the DBCOMMIT value overrides the INSERTBUFF value. The value for INSERTBUFF= must be a positive number.

Default:	1
Note:	if you assign a value that is greater than INSERTBUFF=1, the information written to the log indicating the success or failure might be incorrect. SAS only writes information for a single insert, even when multiple inserts are performed.

MSENGINE= ACE|JET

specifies 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 later 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

PREPARE= YES|NO

NO forces the engine to execute the SQL statement before the Describe action. If you have a linked table to a text file this ensures that the table is found.

Default:

YES

READBUFF=number-of-rows

specifies the number of rows of data to read. Setting a higher value for this option reduces I/O and increases performance, and memory usage. If too many rows are read at once, values returned to SAS might be out of date.

Alias:	ROWSET\|ROWSET_SIZE
Default:	1

SCAN_TEXTSIZE=YES|NO

specifies whether to scan the length of text data for a data source column and use the length of the longest data string as the SAS column width. For Microsoft Excel, this option applies to all character data type columns. For Microsoft Access, this option only applies to the MEMO data type field, it does not apply to the TEXT (less than 256 characters long) field.

YES	scans the length of text data for a data source column. Sets the length of the longest data string as the SAS variable length. If the maximum SCAN_TEXT= length is greater than the maximum DBMAX_TEXT= length, the DBMAX_TEXT= value is set as the SAS variable length. Default for Microsoft Excel workbook.
NO	the column length that is returned from the Microsoft Jet provider is set as the SAS variable length. If the length that is returned from the Microsoft Jet provider is greater than the DBMAX_TEXT= value, the smaller value is set as the SAS variable length. Specify SCANTEXT=NO when you need to update data in a Microsoft Access database or a Microsoft Excel workbook.

Alias:

SCAN_TEXT|SCANTEXT|SCANMEMO

SCAN_TIMETYPE=YES| NO|ANY

specifies whether to scan all row values for a DATETIME data type field to determine the TIME data type based on the setting.

YES	specifies to scan all row values for a DATETIME data type field to determine the TIME data type based on the setting.
NO	turns off the scan function.
ANY	specifies to scan all row values for a DATETIME data type field to determine the TIME data type based on the setting.

Default:	NO
Restriction:	available only for Microsoft Windows.

SPOOL=YES|NO

specifies whether SAS creates a utility spool file during read transactions that read data more than once.

YES	specifies that SAS creates a utility spool file into which it writes the rows that are read the first time. For subsequent passes through the data, the rows are read from the utility spool file rather than being reread from the data source table. This guarantees that the row set is the same for every pass through the data
NO	specifies that the required rows for all passes of the data are read from the data source table. The row set might not be the same for each pass through the data.

Default:

YES

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.

NO	specifies that SAS/ACCESS reads datetime values as numeric date values.
YES	specifies that SAS/ACCESS reads datetime values as character strings.

Default:	NO
Alias:	STRDATES

UNICODE=YES|NO

determines whether the LIBNAME engine binds the character data type columns with narrow or wide character mode. This option supersedes the DBENCODING option. For DBCS (Chinese, Korean, and Japanese) use, it is recommended to use this option instead of the DBENCODING= option.

YES	specifies that SAS binds the character type columns with wide character mode. This allows some character data such as DBCS to be returned correctly. This setting works the same as setting DBENCODING='UTF-16'.
NO	specifies that SAS binds the character type columns with narrow character mode.

Default:

USE_DATETYPE=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\|USEDATE
Default:	YES for Microsoft Excel workbooks.
Default:	NO for Microsoft Access databases.

Top of Page