LIBNAME Options

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

Many of these options are also available as data set options Data Set Options.

ACCESS=READONLY

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

AUTOCOMMIT=YES|NO

determines whether the ACCESS engine commits (saves) updates as soon as the user submits them.

Default:

NO

YES

specifies that updates are committed to a 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.

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

determines whether operations against a single libref share a connection to the data source. Also determines 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 your SAS application.

Use UNIQUE if you want each use of a table to have its own connection.

GLOBALREAD

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

• the participating librefs are created by LIBNAME statements that specify identical values for the CONNECTION and CONNECTION_GROUP options

• the participating librefs are created by LIBNAME statements that specify identical values for any data source connection options.

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

GLOBALREAD is the default value for CONNECTION when you specify CONNECTION_GROUP.

Default:

SHAREDREAD

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=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= is set as TRUE for key set cursors and FALSE for static cursors.

STATIC

specifies that the complete result set is built when you open the cursor, but no changes that are made to the result set are reflected in the cursor. Static cursors are read-only.

Default:

None

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

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 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 (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 only applies when you are reading, appending, and updating character data in a Microsoft Access database or Excel workbook from SAS. Although you can specify a value less than 256, it is not recommended for reading data from Microsoft Access database.

Default:

1,024

DBNULLKEYS=YES|NO

to specify that there might be NULL values in the columns.

YES

if you specify NOT NULL 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 with this syntax:

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

NO

When 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 regardless of whether the column that DBKEY= specifies is defined as NOT NULL:

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.  

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

lets you 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:

NO

DIRECT_SQL=YES|NO|NONE specific-functionality

enables you to specify 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. You can specify 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 turned ON 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

determines the type of access to the Microsoft Excel file. By default, FILELOCK is not set. 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. Specifying FILELOCK=YES indicates that the LIBNAME engine checks and denies the connection if the file was opened by Excel or another application.

YES:	enables 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 another 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.

Enables 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 enables other applications to open the Excel file in READONLY mode with connection information set to browse data.

INSERT_SQL= YES|NO

determines 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 new rows into a table.
NO	specifies that the SAS/ACCESS engine uses an alternate (data source-specific) method to add new rows to a table.

Default:

NO

INSERTBUFF=number-of-rows

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

Default:	1
Note:	If you assign a value that is greater than INSERTBUFF=1, the SAS notes that indicate the success or failure of the insert operation might be incorrect. The SAS notes only represent information for a single insert, even when multiple inserts are performed.

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 of data to read. Setting a higher value for this option reduces I/O and increases performance, and increases memory usage. Additionally, if too many rows are read at once, values returned to SAS might be out of date.

Alias:	ROWSET|ROWSET_SIZE
Default:	1
Restriction:	Microsoft ACCESS only.

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 string data found 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 string of data, 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

does not scan the length of text data for a data source column. 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 lengths. Specify SCANTEXT=NO when you need to update data in the Microsoft Access database or the 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 and automatically determine the TIME data type based on the setting. Option values YES and ANY turn on the scan function. Option value NO turns off the scan function.

YES	specifies that a column with all time values (internal value is less than 1) is assigned a TIME8. format.
NO	the default, specifies that a column with date/time values is assigned the DATE9. format or the DATETIME19. format.
ANY	specifies that a column with any (one or more) time values are assigned a TIME8. format.

See also:	USE_DATETYPE
Restriction:	available only for Microsoft Windows
Default:	NO

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. No spool file is written. There is no guarantee that the row set is 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) users, 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:

NO

USE_DATETYPE=YES|NO

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

YES	specifies that the DATE. format is assigned for 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

Copyright © 2009 by SAS Institute Inc., Cary, NC, USA. All rights reserved.