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

Figure 9.4: SAS Code to Ask for a Prompt to Assign an OLE DB Connection to a Libref.
LIBNAME my_lib OLEDB;
%put %superq(SYSDBMSG);
Figure 9.5: SAS Log Showing Results of Assigning an OLE DB Connection and Saving the
init_string.
NOTE: This session is executing on the X64_8PRO platform.
NOTE: Additional host information:X64_8PRO WIN 6.2.9200 Workstation
NOTE: SAS initialization used:
real time 2.00 seconds
cpu time 1.28 seconds
1 LIBNAME my_lib OLEDB;
NOTE: Libref MY_LIB was successfully assigned as follows:
Engine: OLEDB
Physical Name:
2 %put %superq(SYSDBMSG);
OLEDB: Provider=MSDASQL.1;Persist Security Info=True;Data Source=Excel
Files;Initial Catalog=C:\My_Excel_Files\Excel_Shoes.xlsb
Figure 9.5 shows the init_string needed to process an OLE DB connection. Your output may vary. On the
log listing above, the %PUT command wrote out the “OLEDB” string generated by the LIBNAME
statement. The actual string is prefixed by “OLEDB:” and one or more spaces. This information can be
captured (all except for the OLEDB: and any spaces) to be used as the INIT_STRING= value. Each of the
parameters provided is separated by a semicolon. Spaces are not shown between the parameters, but they
are permitted. Also, notice that the active libraries on the Explorer window now show a libref open for an
Excel file.
Figure 9.6: Data Read from the OLE DB Connection into a SAS Dataset.