Using the SAS LIBNAME to Process Excel Files - Exchanging Data Between SAS and Microsoft Excel - page 53

4.5.6 Other Common SAS PC File LIBNAME Options
The final part of the syntax is for LIBNAME options. They, along with dataset options, modify how the
data elements are processed. The programmer can set the file to be Read-only, increase the default size of
character strings, change how dates are processed, and make several other file-level changes. The options
above applied to the files in their entirety, and the options are set for all data within the file. This next set of
options is not as absolute in the uniform application, and can affect the file or the variables within the file.

ACCESS=READONLY Limits SAS file access to Read-only mode.

●

DBGEN_NAME=DBMS or SAS Specify how data source columns are named.

●

DBMAX_TEXT=n (1-32767) Maximum length of an input character string.*

●

DBSASLABEL=COMPT or NONE Select whether to save input column names as a label.

●

FILELOCK=YES or NO Specify if the input file is locked to other users.

●

SCAN_TEXTSIZE=YES or NO Scan the input file character columns for size of strings.

●

SCAN_TIMETYPE=YES or NO Scan Excel variables for time values.

●

STRINGDATES=YES OR NO Select to read dates as character strings.

●

• USE_DATETYPE=YES or NO

Use the SAS format DATE9. for date values.

*Default=1024.

4.6 Excel-Specific Dataset Options
Another important feature of reading data with a LIBNAME statement is that you have control of dataset
level options. With typical native SAS formatted datasets used with a LIBNAME statement, you are able to
modify what you allow into and out of the dataset when it is opened. While these options are not directly
part of the LIBNAME statement, I like to consider them a feature of using a LIBNAME statement.

The following are dataset options available to Excel files.

DBCREATE_TABLE_OPTS Appends specific SQL code to the CREATE TABLE command.

DBENCODING Defines a different character set to be used in accessing the Excel

file.

DBFORCE Forces insertion of character strings larger than defined length into

the file.

DBGEN_NAME Determines generation of SAS dataset variable names .

DBLABEL Chooses to use SAS variable labels or names as the output column

names.

DBMAX_TEXT Defines the maximum length of a character variable.

DBSASLABEL Chooses to use the source column names as SAS variable labels.

DBSASTYPE Specifies a data type that overrides input Excel default variable

data type.

DBTYPE Specifies a data type that overrides output default SAS variable

data type.

INSERT_SQL Determines the SQL insert method used to add new rows.

READBUFF Determines how many rows are read into the input buffer for

processing.

SASDATEFMT Defines specific date formatting by column when processing dates.

Similar formats must be available in SAS and Excel.