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

Chapter 10: Using PROC SQL to Access Excel
Files
10.1 Introduction............................................................................................... 157
10.2 Purpose ..................................................................................................... 158
10.3 Basic Syntax of the SQL Procedure ........................................................... 158
10.4 A Simple Explanation of SQL “PASS-THROUGH” Processing ..................... 160
10.5 Overview of the Examples ......................................................................... 160
10.5.1 List of Examples ......................................................................................................160
10.6 Examples................................................................................................... 160
Example 10.1 LIBNAME Assignments to Access Excel Using PROC SQL ..................160
Example 10.2 Create an Excel File, Read It with SQL, and Then Compare the Files .161
Example 10.3 Use PROC SQL to Read a Subset of Records from an Excel
Workbook ............................................................................................................................162
Example 10.4 Use PROC SQL Pass-Through Facilities to Process an Excel File.......162
Example 10.5 Read a Pre-defined Range of Cells from an Excel Workbook ..............163
Example 10.6 Calculate a New Variable within the SQL Code and Sort the Output...165
Example 10.7 Examine the Contents and Structure of an Excel Workbook with a
“PCFILES::” Special Query ................................................................................................165
10.7 Conclusion ................................................................................................ 166
10.1 Introduction
Many books have been written about SQL in general and the SAS SQL procedure in particular, and within
those books many pages are devoted to Microsoft Access and SQL. However, the number of pages
dedicated to using PROC SQL to access Excel files is far more limited. SQL and PROC SQL are
considered tools for accessing database file systems where the files are interconnected by primary keys,
secondary keys, and foreign keys, and the results are output as right-joined, left-joined and full-joined data
sets. However, Excel is often overlooked by SQL users. Unlike entering data into an Excel spreadsheet,
getting data into a database system is generally not as easy as double clicking on the file and starting to
type. When you want to add data to a Microsoft Access database, you usually need a tool to enter the data;
it is hard to open an Access database and just start typing. Because of that, the “rows” and “columns” of
files in a Microsoft Access database (they are really called tables) are predictably uniform and considered
fair game for tools like PROC SQL.
Excel is a more open set of files and data storage units (workbooks and worksheets). These are frequently
edited and updated just by typing data into the files. This produces data files that are rarely uniform. They
also often have columns with mixed data types. But, as this document has shown, there are ways to
generate uniform Excel files and read data from these files. This chapter is about using PROC SQL to read
and write data to Excel files. The ins and outs of learning PROC SQL are covered in many other
documents. Therefore, this topic will concentrate on showing you how to open Excel workbooks for
moving data by reading and writing between SAS datasets and Excel workbooks.