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

The following is true of the output shown in Figure 10.6:
● It is the data sent to the Excel file by the SAS DATA step and reread from Excel by PROC SQL.
● The variable names were assigned as labels.
● The character variables now have formats and informats.
• The numeric variables do not have formats or informats.
Figure 10.6: SAS Variable Characteristics of SAS Dataset Read by PROC SQL.
Variable Dataset Type Length Format Informat Label
----------- --------------- ----- ------ --------- ---------- --------------
Region WORK.NEW_SHOES Char 25 $25. $25. Region
Product WORK.NEW_SHOES Char 14 $14. $14. Product
Subsidiary WORK.NEW_SHOES Char 12 $12. $12. Subsidiary
Stores WORK.NEW_SHOES Num 8 Stores
Sales WORK.NEW_SHOES Num 8 Sales
Inventory WORK.NEW_SHOES Num 8 Inventory
Returns WORK.NEW_SHOES Num 8 Returns
Example 10.3 Use PROC SQL to Read a Subset of Records from an Excel
Workbook
The code in Figure 10.7 uses the Excel workbook that was created in Example 10.2 by opening it with a
LIBNAME statement. Then PROC SQL read a subset of the records and created a SAS dataset with those
Excel rows. The comparison output is not shown because it looks very similar to Figure 10.6, but these
SAS datasets contain only the data where Region equals Africa.
Figure 10.7: SAS Variable Characteristics.
LIBNAME out_data pcfiles path="C:\My_Excel_Files\Excel_Shoes.xlsb";
PROC SQL;
CREATE TABLE New_shoes AS
SELECT *
FROM out_data."SHOES$"n
WHERE region='Africa';
QUIT;
PROC COMPARE
BASE=SASHELP.shoes(where=(region='Africa'))
COMP=New_Shoes;
RUN;
Example 10.4 Use PROC SQL Pass-Through Facilities to Process an Excel
File
This example uses the PCFILES engine for the connection to Excel. The code shown in Figure 10.8 is
reading a named range of cells (called my_range) assigned by Excel to the cells B1 to G17 of an Excel
workbook that contains the shoes data.
In the SAS code the PATH= option takes the place of the LIBNAME statement for defining the Excel file.
The range named “ my_range ” is shown in Figure 10.8 below. This example takes advantage of the fact
that the first row of data values from the Excel file are used as the variable names. Other ranges that do not
include the first data row may produce unpredictable SAS variable names while opening the file because of
the fact that the SAS normally uses first row of the input range to define the variable names. See Example
10.5 for a workaround the converts a blank row in the names range to special SAS variable names.