SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition

Overview

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 do the following:

• retrieve and manipulate data that is 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 retrieve DBMS data.

The following figure summarizes the variety of source material that you can use with

PROC SQL and what the procedure can produce.

Figure 7.1 PROC SQL Input and Output

PROC SQL tables

(SAS data files)

SAS data views

(PROC SQL views)

(DATA step views)

(SAS/ACCESS views)

DBMS tables

DBMS tables

reports

PROC SQL views

PROC

SQL

PROC SQL tables

(SAS data files)

macro variables

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 By Using

the SQL Procedure Pass-Through Facility” on page 171. The SAS/ACCESS LIBNAME

statement is described in “Connecting to a DBMS By Using the LIBNAME Statement”

on page 168.

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.

216 Chapter 7 • SQL Procedure