The SQL
procedure implements Structured Query Language (SQL) for the SAS System.
SQL is a standardized, widely used language that retrieves and updates data
in tables and views based on those tables.
The SAS System's SQL procedure enables you to
- retrieve and manipulate data that are stored in tables or
views.
- create tables, views, and indexes on columns in tables.
- create SAS macro variables that contain values from rows in a
query's result.
- add or modify the data values in a table's columns or insert and
delete rows. You can also modify the table itself by adding, modifying, or
dropping columns.
- send DBMS-specific SQL statements to a database management system
(DBMS) and to retrieve DBMS data.
PROC SQL Input and Output summarizes the variety of source material
that you can use with PROC SQL and
what the procedure can produce.
PROC SQL Input and Output
![[IMAGE]](./images/01335563.gif)
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 data views,
or from DBMS tables using PROC SQL's Pass-Through Facility. The Pass-Through
Facility is described in
Connecting to a DBMS Using the SQL Procedure Pass-Through Facility
.
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.
A SAS data 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 are stored elsewhere. There are
three types of SAS data views:
- PROC SQL views
- SAS/ACCESS views
- DATA step
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 accessed by a view are a subset or superset of the data in its underlying
table(s) or view(s).
A PROC SQL view is a SAS data set of type VIEW 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.
You can update data through a PROC SQL or SAS/ACCESS view with certain
restrictions. See Updating PROC SQL and SAS/ACCESS Views .
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]](../common/images/cautend.gif)
Because
PROC SQL implements Structured Query Language, it works somewhat differently
from other base SAS procedures, as described here:
- You do not need to repeat the PROC SQL statement with each SQL
statement. You need only to repeat the PROC SQL statement if you execute
a DATA step or another SAS procedure between statements.
- SQL procedure statements are divided into clauses. For example,
the most basic SELECT statement contains the SELECT and FROM clauses. Items
within clauses are separated with commas in SQL, not with blanks as in the
SAS System. For example, if you list three columns in the SELECT clause,
the columns are separated with commas.
- The SELECT statement, which is used to retrieve data, also outputs
the data automatically unless you specify the NOPRINT option in the PROC SQL
statement. This means you can display your output or send it to a list file
without specifying the PRINT procedure.
- The ORDER BY clause sorts data by columns. In addition, tables
do not need to be presorted by a variable for use with PROC SQL. Therefore,
you do not need to use the SORT procedure with your PROC SQL programs.
- A PROC SQL statement runs when you submit it; you do not have
to specify a RUN statement. If you follow a PROC SQL statement with a RUN
statement, the SAS System ignores the RUN statement and submits the statements
as usual.
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.
