Database Reference
In-Depth Information
PROMPT
Enables SAS to provide help menus to assist with assigning librefs.
●
•
VERSION
Assigns the output Excel version; SAS can determine the input version.
The following SAS/ACCESS LIBNAME options are commonly used with Excel. However, space does not
permit an example of each option, so I have selected a few values and provided a short description of each
here. Other options are available for different input file types and access methods. The defaults for Excel
input files are listed in
BOLD
typeface. Aliases may also exist for these options.
ACCESS=READONLY
Limits SAS 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.
●
•
USEDATETYPE=
YES
or NO
Use the SAS format DATE9. for date values.
*Default=1024.
4.5 LIBNAME Statement ENGINE CONNECTION OPTION Descriptions
The next part of the LIBNAME statement we will examine here is the way that SAS can open the Excel
files. By examining how these options interact with Excel workbooks, I will show how the options treat the
data when it is accessed after the Excel file is open. In the LIBNAME syntax chart listed in section 4.4 we
see SAS/ACCESS engine connection options and SAS/ACCESS LIBNAME statement options. These are
both little-used features of SAS LIBNAME statements. The example portion of this chapter shows some
examples and outputs from applying these options
4.5.1 HEADER Option to Read Variable Names
The first to be addressed is the set of SAS/ACCESS engine connection options. See Example 4.1, Using the
Engine Connection HEADER Option. The HEADER option affects the reading of the first line of the Excel
file. When the option value is set to YES (the default), the first row of data is read to build SAS variable
names. The cell values are converted to SAS variable names. When the text values would not generate
valid SAS variable names, underscores are inserted into the SAS variable name. Duplicate names are
avoided by adding a number to the end of the variable name. This number increases as duplicate columns
are found, moving from left to right.
When the
HEADER
option is set to
NO
or when the cell value does not convert to a text field, SAS
generates a variable name. Also, when the option is set to
NO,
then the first row is considered data, and
variable names are generated as F1, F2, F3, ... , to F
n
with the
n
being the number of the last column in the
Excel spreadsheet.
4.5.2 MIXED Option to Select Data Types
The
MIXED
option is provided to assist in reading fields that are not obviously text or numeric. Excel files
can be generated by someone typing data into one cell after another without regard to the type of data that
they are entering (character or numeric). SAS has to make a default best guess about what the field
contains. When an Excel input column contains both character and numeric data, the intent of this option is
to read all of the data in the Excel column as character data. This option is handled by the Microsoft Jet or
ACE provider and is available only in Windows for Excel files. This does not work for delimited files;
remember, delimited files are read with a FILENAME statement.
Search WWH ::
Custom Search