O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required