Using PROC SQL to Access Excel Files - Exchanging Data Between SAS and Microsoft Excel - page 163

Figure 10.8: SAS SQL Reading a Named Range of Excel Cells.
PROC SQL;
CONNECT TO pcfiles AS db (PATH="C:\My_Excel_Files\Shoes_1.xls");
SELECT * FROM CONNECTION TO db
(
SELECT * FROM "my_range"n
);
DISCONNECT FROM db;
QUIT;
Figure 10.9: The Input Excel File.
The SQL code in Figure 10.8 does not create a file. Instead, it just writes the data to the output listing
window (or with the default settings for SAS 9.3 and above, it will write the data to an HTML display).
Notice that the range “my_range” is case-sensitive in the code and here includes the top row of the Excel
worksheet, but not the first column of the worksheet. The workbook in Figure 10.9 was the input file for
this task.
Example 10.5 Read a Pre-defined Range of Cells from an Excel Workbook
This example uses a PCFILES engine for the connection to Excel. The code reads a named range of cells
(called New_range2) assigned by Excel. This range includes cells copied from [B10 to E24] to [I10 to L24]
of an Excel workbook that contains the shoes data. This named range does not include a title row with
variable names, but it does include a blank row of cells at the top of the range (starting at I9). This
simulates a case where you may have to read a small group of cells from a spreadsheet, or even several
groups of cells. SAS changes the way that SQL reads the spreadsheet and creates the SAS variable names.
When the first row is blank, the names and labels are assigned the values F1, F2, F3 etc.