SAS LIBNAME Options for PCFILES on Linux, UNIX, and Microsoft
Windows
The following SAS LIBNAME statement options provide
enhanced control over the way that SAS processes PC files data.
Details
Note: Note that these are advanced
options that do not need to be specified for many of the tasks that
you perform.
- 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.
- 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
-
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.
- 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 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.
- 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.
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 _COL n, where n is the column number (starting with zero).
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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. In addition, 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 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.
Alias:SCANTEXT | SCANMEMO
Default:YES for a Microsoft Excel workbook
NO for a Microsoft Access database
- 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.
- 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
- 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.
Alias:USE_DATE | USEDATE
Default:YES for a Microsoft Excel workbook
NO for a Microsoft Access database