Accessing Excel with OLE DB or ODBC Application Program Interfaces (API Methods) - Exchanging Data Between SAS and Microsoft Excel - page 154

Example 9.3 Using an OLE-DB init_string to Open an Excel Workbook
By using the output from the “%put %superq(SYSDBMSG);” to build a new LIBNAME statement that
looks something like this (with one parameter per line), we can reassign the libref again using SAS code in
our programs.
Figure 9.7: SAS Code to Open an OLE-DB Connection to an Excel File.
libname my_lib oledb preserve_tab_names=yes
init_string="
Provider=MSDASQL.1;
Persist Security Info=True;
Data Source=Excel Files;
Initial Catalog=C:\My_Excel_Files\Excel_Shoes.xlsb";
%put %superq(SYSDBMSG);
The output SAS log looks something like Figure 9.8 with the actual init_string crossed out so that no one
can copy your information.
Figure 9.8: LIBNAME Assignment Using the OLE DB init_string= Option.
1 libname my_lib oledb preserve_tab_names=yes
2 init_string=X
3 XXXXXXXXXXXXXXXXXXX
4 XXXXXXXXXXXXXXXXXXXXXXXXXXX
5 XXXXXXXXXXXXXXXXXXXXXXXX
6 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX;
NOTE: Libref MY_LIB was successfully assigned as follows:
Engine: OLEDB
Physical Name:
7 %put %superq(SYSDBMSG);
Example 9.4 Using PROC CONTENTS to Verify Excel to OLE DB Connection
Using PROC CONTENTS you can determine if the data from the Excel workbook was able to be read by
SAS. See Figure 9.9 for the code.
Figure 9.9: PROC CONTENTS Code to Verify That the LIBNAME Connection Using OLE DB
Is Working.
proc contents data=my_lib._all_;
run;