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

We will use these clauses to connect to Excel files. They are also useful to connect to other database
systems like Microsoft Access. We will examine some PCFILES query options and JET/ACE query
options. For you to connect to an external database using the pass-through facility, the following must
occur:
Start PROC SQL.
Use the PCFILES engine with a CONNECT clause (and optionally any alias).
Provide any arguments and/or attributes required.
Issue the CONNECTION TO statement.
The following SAS PROC SQL syntax is used to connect to another data source, usually a different data
base system. The CONNECTION TO statement below allows you to connect to another database system
on either your current computer or another remote computer that could be thousands of miles away.
Figure 10.1: SQL Code Syntax.
SELECT column-list
FROM CONNECTION TO data-source-name AS <alias>
<database-connection-arguments;>
The “CONNECTION TO” option in SQL usually means you want to connect to something other than a
SAS dataset. Example 10.4 below will show how to connect to an Excel workbook using this method.
Knowing how to do this kind of connection is really useful, since this is a book about moving data between
SAS and Excel.
The SQL command in Figure 10.2 is a little different from the others. Notice the “SELECT FROM”
keywords that precede the CONNECTION TO statement. The power of this statement comes from the fact
that the “SELECT FROM” pair can have nested commands. The code in Figure 10.2 has two “SELECT”
and two “FROM” commands.
Figure 10.2: SQL Code Syntax.
SELECT column-list (SELECT
FROM . . .
WHERE . . .
GROUP BY . . .
HAVING . . .
ORDER BY)
FROM CONNECTION TO ....<other SQL commands>
The PCFILES Special Queries
These queries have a special format that includes the case-sensitive constant “PCFILES::” (yes, both colons
are required) in front of the query name and arguments. Also note that the arguments themselves are case-
sensitive and must be entered that way. An argument “OneTwoThree” is not the same as “onetwothree”.
When submitted to the query, the parameters in Table 10.6.1 below are enclosed in quotation marks like
(“parameter”). Example 10.7 deals with some of these queries.
The JET or ACE Special Queries
These queries have a special format that includes one of the case-sensitive constants “JET::” or “ACE::” in
front of the query name and arguments. Also note that the arguments themselves are case-sensitive and
must be entered that way. An argument “OneTwoThree” is not the same as “onetwothree”. When submitted
to the query, the parameters are enclosed in quotation marks (“parameter”).