LIBNAME Statement: PC Files on Linux, UNIX, and 64-Bit Microsoft Windows

SAS LIBNAME Options for PC Files on Linux and UNIX

The following SAS LIBNAME statement options provide enhanced control over the way that SAS processes PC files data. See Syntax for PC Files on Linux, UNIX, and 64-Bit Microsoft Windows.

Many of these LIBNAME options are also available as data set options. See Data Set Options for PC Files on Linux and UNIX, and 64-Bit Windows.

Note: Note that these are advanced options that do not need to be specified for many of the tasks that you perform. [cautionend]

CURSOR_TYPE=DYNAMIC|FORWARD_ONLY|KEYSET_DRIVEN|STATIC

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

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 SQL server interfaces.
FORWARD_ONLY: specifies that the cursor behaves like a DYNAMIC cursor, except that it supports only fetching of rows sequentially.
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 scroll around the cursor.
STATIC: specifies that the complete result set is built when you open the cursor. 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

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 they are submitted.

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.

Default:

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= option and CONNECTION_GROUP= option.
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= option when you specify CONNECTION_GROUP= option.

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=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 the SQL Server interfaces.
FORWARD_ONLY: specifies that the cursor behaves like a DYNAMIC cursor, except that it only supports fetching the rows sequentially.
KEYSET_DRIVEN: specifies that the cursor determines which rows belong to the result set when the cursor is opened. Note that 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

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.

Note: If you specify both DBCOMMIT= and ERRLIMIT=, the DBCOMMIT= is issued before the rollback. Because the DBCOMMIT= option is issued before the rollback, the DBCOMMIT= option overrides the ERRLIMIT= option (rollback) in this situation. [cautionend]

Default:

1,000 (inserting) or 0 (updating; commit occurs when data set or procedure completes)

DBGEN_NAME=DBMS| SAS

specifies that the data source columns are renamed and the format that the 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 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 a Microsoft Access database.

Default:

1,024

DBNULLKEYS=YES|NO

specifies column definitions.

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.

If you specify DBNULLKEYS=YES and a column that is not defined as NOT NULL in the DBKEY= option, SAS generates a WHERE clause that finds NULL values.

If you specify DBKEY=COLUMN and COLUMN is not defined as NOT NULL, SAS generates a WHERE clause that enables SAS to prepare the statement once. The statement can be used any value in the column. For example WHERE ((COLUMN =?) OR ((COLUMN IS NULL) AND (? IS NULL))); .

Note:

This syntax has the potential to be much less efficient than the shorter form of the WHERE clause.

NO

When you specify DBNULLKEYS=NO or specify a column as NOT NULL in the DBKEY= option, SAS generates a simple WHERE clause. If you know that there are no NULL values for the columns that you specify in the DBKEY= option, you can use DBNULLKEYS=NO.

If you specify DBNULLKEYS=NO and DBKEY=COLUMN, SAS generates a shorter form of the WHERE clause. This is true whether DBKEY=COLUMN is defined as NOT NULL or not.

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 you are reading data into SAS from the data source.

Default:

COMPAT

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:

DIRECT_SQL=YES|NO|NONE| specific-functionality

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. This option does not affect outer joins of two tables.

Note: This option is always turned ON for the Jet engine. [cautionend]

NOGENSQL

prevents PROC SQL from generating SQL to pass 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

INSERTBUFF=number-of-rows

specifies the number of rows for a multiple-row insert. The value for INSERTBUFF= must be a positive number. If the INSERTBUFF= value is greater than the DBCOMMIT= value, the DBCOMMIT= value overrides it. If you assign a value that is greater 1, the SAS application notes that indicate the success or failure of the insert operation might be incorrect. Notes generated by SAS represent information for a single insert. This is also true when multiple inserts are performed.

Default:

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

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.

YES

scans the length of text data for a data source column and use the length of the longest string data found as the SAS variable width. If the maximum length found is greater than what is specified in the DBMAX_TEXT= option, the smaller value is applied as the SAS variable length.

For Excel, this option applies to all character data type columns. For Microsoft Access, this applies only to the MEMO data type field. It does not apply to the TEXT (less than 256 characters long) field.

NO

does not scan the length of text data for a data source column. The column length returned from the Jet provider is used as the SAS variable width. If the returned length is greater than the length specified with the DBMAX_TEXT= option, the smaller value is assigned to the SAS variable length.

Note: Specify SCANTEXT=NO when you need to update data in the Microsoft Access database or Excel workbook. [cautionend]

Default:

YES for a Microsoft Excel workbook

NO for a Microsoft Access database

Alias:

SCANTEXT| SCANMEMO

SCAN_TIMETYPE=YES |NO

specifies whether to scan all row values for a DATE or TIME data type field, and automatically determine the TIME data type based on the setting. Option values YES turn on the scan function. Option value NO turns off the scan function.

YES: specifies that a Microsoft Excel column with all time values (internal value is less than 1) is assigned a TIME8. format.
NO: specifies that the SCAN function is not enabled.

Alias:	SCAN_TIME\| SCANTIME
Default:	NO
See:	USE_DATETYPE

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.

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

Default:	NO
Alias:	STRDATES

USE_DATETYPE=YES|NO

specifies whether to use DATE9. format for date columns in the data source table while importing data from a Microsoft Excel workbook. Specifies whether to use a format for date columns in the data source table while importing data from a Microsoft Access table

YES: specifies that the SAS DATE9. format is assigned for date columns in Excel data source table.
NO: specifies that the SAS DATE formats are not assigned to the data source table.

Default:	YES for a Microsoft Excel workbook NO for a Microsoft Access database
Alias:	USE_DATE\|USEDATE

Top of Page