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

Figure 10.11b: SAS Output Dataset from Reading the Named Range from Excel.
Example 10.6 Calculate a New Variable within the SQL Code and Sort the
Output
This example shows how to calculate a new variable within the SQL code. Here the calculation is done
after the data is returned from the connection to Excel. There is no table created here; the data is just
printed.
Figure 10.12: SQL Code to Calculate a New Variable from an Excel Workbook.
Proc SQL ;
CONNECT TO PCFILES AS db (PATH="C:\My_Excel_Files\Shoes_2.xls");
SELECT * ,Total_Sales - Total_Returns as net_sales
FROM CONNECTION TO DB
(SELECT * FROM "Shoes$"n
WHERE region='Asia'
ORDER BY product);
DISCONNECT FROM DB;
QUIT;
Example 10.7 Examine the Contents and Structure of an Excel Workbook
with a “PCFILES::” Special Query
Figure 10.13 shows a set of three “PCFILES::” special queries in one PROC SQL call. The first query
shows the columns in the worksheet “Shoes$”, the second shows the columns in all of the Worksheets and
ranges in the workbook, and the third query shows all of the tables in the workbook.