LIBNAME Options

provides additional control over the way that SAS processes PC files data.
Interaction: For many tasks that you perform, you do not need to specify any of these advanced options. Many of these options are also available as data set options.
Syntax

Syntax

Optional Arguments

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.
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.
Alias:TIMEOUT
Default:0 (no time-out)
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 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
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.
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 a 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 enables 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 do either of the following steps:
  • specify DBNULLKEYS= YES and a column not defined NOT DBNULLKEYS= data set option, SAS generates a WHERE clause to find NULL values.
  • specify DBKEY= and COLUMN is not defined as NOT NULL, SAS generates a WHERE clause similar to the following:
    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.
  • 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.
  • 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.
Default: YES
Note: This syntax enables SAS to prepare the statement once and use it for any value, NULL, or NOT NULL in the column.
Example:
Example WHERE (COLUMN = ?)
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:NO
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.
  • 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
Restriction:The NOMULTOUTJOINS option does not affect outer joins of two tables.
Note: The NOMULTOUTJOINS option is always set for the Microsoft Jet engine.
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.
By setting FILELOCK= YES with ACCESS= READONLY, this enables other applications to open the Excel file in READONLY mode with connection information set to browse data.
Restriction: Applies only to the Microsoft Excel LIBNAME engine; has no effect on the Microsoft Access LIBNAME engine.
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:NO
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.
Default:ACE
Restriction:It is recommended that you do not use this file option unless you are trying to create a 95 format file.
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
SCANTEXT= 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 specifies that 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 | SCANMEMO | SCAN_TEXTSIZE
SCANTIME= 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.
Alias: SCAN_TIME | SCAN_TIMETYPE
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.
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
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: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:YES for Microsoft Excel workbooks.

NO for Microsoft Access databases.

Copyright © SAS Institute Inc. All rights reserved.