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

• When a PROC SQL statement is executed, PROC SQL continues to run until a

QUIT statement, a DATA step, or another SAS procedure is executed. Therefore, you

do not need to repeat the PROC SQL statement with each SQL statement. You need

to repeat the PROC SQL statement only if you execute a QUIT statement, a DATA

step, or another SAS procedure between SQL 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 other SAS code. For

example, if you list three columns in the SELECT clause, then the columns are

separated with commas.

• The SELECT statement, which is used to retrieve data, also automatically writes the

output data to the Output window unless you specify the NOPRINT option in the

PROC SQL statement. Therefore, 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, then SAS

ignores the RUN statement and submits the statements as usual.

Threaded Processing Using PROC SQL

The THREADS option enables or disables parallel processing in PROC SQL. Threaded

processing achieves a degree of parallelism in a processing operation. This parallelism is

intended to reduce the real time to completion for a processing operation and therefore

limit the cost of additional CPU resources.For more information, see “Support for

Parallel Processing” in SAS Language Reference: Concepts.

The value of the SAS system option CPUCOUNT= affects the performance of the

threaded sort. CPUCOUNT= suggests how many system CPUs are available for use by

the threaded procedures.

For more information about the THREADS option, see “THREADS | NOTHREADS”

on page 230.

For more information, see “CPUCOUNT= System Option” in SAS System Options:

Reference.

Syntax: SQL Procedure

Tips: You can use any global statements. For more information, see “Fundamental

Concepts for Using Base SAS Procedures” in Base SAS Procedures Guide.

You can use data set options anytime a table name or view name is specified. For

more information, see “Using SAS Data Set Options with PROC SQL” on page 156.

Regular type indicates the name of a component that is described in Chapter 8,

“SQL Procedure Components,” on page 315. view-name indicates a SAS view of

any type.

PROC SQL <option(s)>;

ALTER TABLE table-name

218 Chapter 7 • SQL Procedure

<ADD column-definition-1 <, column-definition-2, …>>

<ADD CONSTRAINT constraint-name-1 constraint-specification-1

<, constraint-name-2 constraint-specification-2, …>>

<ADD constraint-specification-1 <, constraint-specification-2, …>>

<DROP column-1 <, column-2, …>>

<DROP CONSTRAINT constraint-name-1 <, constraint-name-2, …>>

<DROP FOREIGN KEY constraint-name>

<DROP PRIMARY KEY>

<MODIFY column-definition-1 <, column-definition-2, …>>

;

CREATE <UNIQUE> INDEX index-name

ON table-name (column-1 <, column-2, …>);

CREATE TABLE table-name

(column-specification-1 <, column-specification-2 | constraint-specification-1, …

>);

CREATE TABLE table-name LIKE table-name-2;

CREATE TABLE table-name AS query-expression

<ORDER BY order-by-item-1 <, order-by-item-2, …>>;

CREATE VIEW proc-sql-view <(column-name-list)> AS query-expression

<ORDER BY order-by-item-1 <, order-by-item-2, …>>

<USING libname-clause-1 <, libname-clause-2, …>> ;

DELETE FROM table-name | sas/access-view | proc-sql-view

<AS alias>

<WHERE sql-expression>;

DESCRIBE TABLE table-name-1 <, table-name-2, …>;

DESCRIBE VIEW proc-sql-view-1 <, proc-sql-view-2, …>;

DESCRIBE TABLE CONSTRAINTS table-name-1 <, table-name-2, …>;

DROP INDEX index-name-1 <, index-name-2, …> FROM table-name;

DROP TABLE table-name-1 <, table-name-2, …>;

DROP VIEW view-name-1 <, view-name-2, …>;

INSERT INTO table-name | sas/access-view | proc-sql-view

<(column-1<, column-2, …>)>

SET column1=sql-expression-1 <, column-2=sql-expression-2, …>

<SET column1=sql-expression-1 <, column-2=sql-expression-2, …> …>;

INSERT INTO table-name | sas/access-view | proc-sql-view

<(column-1 <, column-2, …>)>

VALUES (value-1 <, value-2, …>)

<VALUES (value-1 <, value-2, …>) …>;

INSERT INTO table-name | sas/access-view | proc-sql-view

<(column-1 <, column-2, …>)> query-expression;

RESET <option(s)>;

Syntax: SQL Procedure 219