Previous Page | Next Page

The SQL Procedure

Overview: SQL Procedure

What Is the SQL Procedure?

The SQL procedure implements Structured Query Language (SQL) for SAS. SQL is a standardized, widely used language that retrieves data from and updates data in tables and the views that are based on those tables.

The SAS SQL procedure enables you to

The following figure summarizes the variety of source material that you can use with PROC SQL and what the procedure can produce.

PROC SQL Input and Output

[PROC SQL Input and Output]

What Are PROC SQL Tables?

A PROC SQL table is synonymous with a SAS data file and has a member type of DATA. You can use PROC SQL tables as input into DATA steps and procedures.

You create PROC SQL tables from SAS data files, from SAS views, or from DBMS tables by using PROC SQL's Pass-Through Facility or the SAS/ACCESS LIBNAME statement. The Pass-Through Facility is described in Connecting to a DBMS Using the SQL Procedure Pass-Through Facility in the SAS 9.2 SQL Procedure User's Guide. The SAS/ACCESS LIBNAME statement is described in Connecting to a DBMS Using the LIBNAME Statement in the SAS 9.2 SQL Procedure User's Guide.

In PROC SQL terminology, a row in a table is the same as an observation in a SAS data file. A column is the same as a variable.

What Are Views?

A SAS view defines a virtual data set that is named and stored for later use. A view contains no data but describes or defines data that is stored elsewhere. There are three types of SAS views:

You can refer to views in queries as if they were tables. The view derives its data from the tables or views that are listed in its FROM clause. The data that is accessed by a view is a subset or superset of the data that is in its underlying tables or views.

A PROC SQL view is a SAS data set of type VIEW that is created by PROC SQL. A PROC SQL view contains no data. It is a stored query expression that reads data values from its underlying files, which can include SAS data files, SAS/ACCESS views, DATA step views, other PROC SQL views, or DBMS data. When executed, a PROC SQL view's output can be a subset or superset of one or more underlying files.

SAS/ACCESS views and DATA step views are similar to PROC SQL views in that they are both stored programs of member type VIEW. SAS/ACCESS views describe data in DBMS tables from other software vendors. DATA step views are stored DATA step programs.

Note:   Starting in SAS System 9, PROC SQL views, the Pass-Through Facility, and the SAS/ACCESS LIBNAME statement are the preferred ways to access relational DBMS data; SAS/ACCESS views are no longer recommended. You can convert existing SAS/ACCESS views to PROC SQL views by using the CV2VIEW procedure. See the CV2VIEW Procedure in SAS/ACCESS for Relational Databases: Reference for more information.  [cautionend]

You can update data through a PROC SQL or SAS/ACCESS view with certain restrictions. See Updating PROC SQL and SAS/ACCESS Views in the SAS 9.2 SQL Procedure User's Guide.

You can use all types of views as input to DATA steps and procedures.

Note:   In this chapter, the term view collectively refers to PROC SQL views, DATA step views, and SAS/ACCESS views, unless otherwise noted.  [cautionend]

Note:   When the contents of an SQL view are processed (by a DATA step or a procedure), the referenced data set must be opened to retrieve information about the variables that is not stored in the view. If that data set has a libref associated with it that is not defined in the current SAS code, then an error will result. You can avoid this error by specifying a USING clause in the CREATE VIEW statement. See CREATE VIEW Statement for details.  [cautionend]

Note:   When you process PROC SQL views between a client and a server, getting the correct results depends on the compatibility between the client and server architecture. For more information, see Accessing a SAS View in the SAS/CONNECT User's Guide.  [cautionend]

SQL Procedure Coding Conventions

Because PROC SQL implements Structured Query Language, it works somewhat differently from other Base SAS procedures, as described here:

Previous Page | Next Page | Top of Page