Lesson Summary of Performing Queries Using SQL

Performing Queries Using SQL

Lesson Summary

This page contains

a text summary of the material taught in the lesson
syntax for statements and options
sample programs
points to remember.

I. Text Summary

To go to the page where a task, programming feature, or concept was presented, select a link.

PROC SQL Basics
PROC SQL uses statements written in Structured Query Language, or SQL, a standardized language that is widely used to retrieve and update data in tables and views based on those tables. When you want to examine relationships between data values, subset your data, or compute values, the SQL procedure provides an easy, flexible way to analyze your data.

Writing a PROC SQL Step
Before creating a query, you must assign a libref to the SAS data library that stores the table to be used. Then you submit a PROC SQL step. You invoke the SQL procedure using the PROC SQL statement.

The SELECT Statement
After you invoke the SQL procedure, the SELECT statement performs the tasks of retrieving and displaying data. It is composed of clauses, including the

SELECT clause, which selects columns and creates new columns
FROM clause, which names the table(s) from which to retrieve rows
WHERE clause, which selects rows based on a condition
ORDER BY clause, which orders the rows by selected variables.

Querying Multiple Tables
You can use a PROC SQL step to query data stored in two or more tables. In SQL terminology, this is called "joining" tables. To join multiple tables

specify a prefix in the SELECT statement if both tables that you are querying contain like-named columns
specify each table name in the FROM clause
use the WHERE clause to select rows, from two or more tables, based on a condition
use the ORDER BY clause to sort rows of two or more tables by values of selected columns.

Ending the SQL Procedure
PROC SQL is an interactive procedure. A status line in the Program Editor window, "PROC SQL running," informs you that the procedure is active. To end the SQL procedure, you can submit

another PROC step
a DATA step
a QUIT statement.

II. Syntax

To go to the page where a statement or option was presented, select a link.

LIBNAME libref 'SAS-data-library';

PROC SQL;

SELECT column-1 <, . . . column-n,>

FROM table-1 | view-1 <, . . . table-n | view-n,>

<WHERE expression>

<ORDER BY column-1 <, . . . column-n,>>;

III. Sample Programs

     proc sql;
        select id,lastname,netpay,grosspay,
               grosspay*.06 as bonus
           from emplib.payroll
           where netpay>25000
           order by lastname;
     quit;

     proc sql;
        select bldginfo.id,lastname,building,room,extension
           from emplib.payroll,emplib.bldginfo
           where bldginfo.id=payroll.id
           order by lastname;
     quit;

IV. Points to Remember

You do not need to use a RUN statement with the SQL procedure.
Do not end a clause with a semicolon unless it is the last clause in the SELECT statement.
When you join multiple tables, be sure to specify matching columns in the WHERE clause to avoid unwanted combinations.
To end the SQL procedure, you can submit another PROC step, a DATA step, or a QUIT statement.