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

10.2 Purpose
Within this chapter I intend to show how PROC SQL can locate and access the data within and about a
Microsoft Excel workbook. The Structured Query Language standard that defines SQL and PROC SQL has
been developed over a long period of time. The SQL standard is an independent query language intended to
run on multiple operating systems and computer hardware. Some implementations of SQL extend the
features and options to meet the needs and features of their system. For example, some date functions of
Oracle SQL software do not produce the same output as similar date features of the SAS SQL
implementation. While on the surface this seems like a problem, the SQL standard allows a feature called
“Pass-Through”. This allows the user to send instructions from one SQL platform to another. An example
of this would be a SAS SQL user sending a command to retrieve a date value as it was formatted in Oracle
directly to a SAS SQL table that was created.
10 . 3 Basic Syntax of the SQL Procedure
PROC SQL <options-list>
CONNECT TO data-source-name AS <alias>
<(connect-statement-arguments)>,<(database-connection-arguments)>
DISCONNECT FROM <data-source-name> <alias>
EXECUTE (data-source-specific-SQL-statement)
BY <data-source-name> <alias>
SELECT column-list
FROM CONNECTION TO data source-name AS <alias>
<database-connection-arguments;>
Ordering of the SELECT Statement Commands
When you build an SQL SELECT statement, the SQL syntax for clauses that you use dictates that they
must appear in this order. The SQL system will verify that the commands are in the proper order before
proceeding to execute the submitted SQL commands. These clauses are not discussed here because they are
a part of the normal SQL syntax and do not directly relate to using Excel specifically. Only the SELECT
and FROM clauses are required in order to use an SQL SELECT statement.
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
In the SQL syntax definition above, the clauses below are used to connect to an external database.
● CONNECT TO
● DISCONNECT FROM
● EXECUTE BY
• CONNECTION TO