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

you are using. The engine connection option PATH= (in conjunction with the <engine> option of the
LIBNAME statement) allows SAS to select access methods that are appropriate for your configuration.
All of this may seem a little confusing because the “<physical-file-name>” is listed separately as
part of the LIBNAME statement, and the PATH option syntax as shown in Example 4.3, Using the Engine
Connection PATH Option, also includes a data path and file name. If the PATH option is used, “ PATH= ”
should immediately precede the “<physical-file-name>” and the LIBNAME statement can
include only one “<physical-file-name>” . More important, when you use the PATH option you
must also include an engine option on the LIBNAME statement.
Some engine names used to access Excel workbooks in conjunction with the PATH option are:
● EXCELCS
• PCFILES
The PCFILES engine resolves bit configuration differences through the SAS PC Files Server, which is a
background windows service that can be configured to start when your computer starts. Example 4.3, Using
the Engine Connection PATH Option, will show you how to use this option.
4.5.4 VERSION Option to Identify Excel File Version
The VERSION option is not required on input because the Microsoft JET/ACE provider engines can
determine the format of the input file. However, the output format can be chosen to be one of the three
output Excel file formats (Excel 5, Excel 97 - 2003, and Excel 2007+). The default version is '97'. The
actual option codes are listed here '2007','2003', '2002', '2000', '97', '95', '5' (the quotes are valid but not
required.) NOTE - 2007 is valid only for SAS 9.2 Mod 2 and later. The Excel 2010 and Excel 2013
formats are compatible with the file format for Excel 2007.
4.5.5 PROMPT Option to Interactively Assign a Libref
This option can be used to interactively assign a file to a libref. There are several options available for this
command. I would not like to assign a libref to a file using interactive prompts every time that I use the file.
But it turns out that this option does have a useful feature. It will provide access to an initialization string of
characters that is useful when using the ODBC data sources. Both an Excel file and an Oracle database or
other databases can be accessed this way. When you use the PROMPT method to connect to an ODBC data
source, you can issue the following command to display the initialization string on the log.
%PUT %SUPERQ (SYSDBMSG)
Because each string is different, no example will be shown here. Also, because of the advanced nature of
this process, it should be used only when the exact syntax is known for all of the connection options
required for the ODBC data source being accessed.
The options available are the following.
● Yes
● No
● Required
● Noprompt
● Prompt
• UDL
NOTE: When you use PROMPT=NO or PROMPT=NOPROMPT, a “<physical-file-name>”
or PATH= for file identification is required.
A “UDL” is a Microsoft Datalink File, which is not discussed here.