The SQL Procedure

Procedure Syntax

Tip:

Supports the Output Delivery System. (See Output Delivery System for details.)

Reminder:

See Statements with the Same Function in Multiple Procedures for details. You can also use any global statements as well. See Global Statements for a list.

Note:

Regular type indicates the name of a component that is described in Component Dictionary .

view-name indicates a SAS data view of any type.

PROC SQL <option(s)>;

ALTER TABLE table-name
<constraint-clause> <,constraint-clause>...>;

<ADD column-definition <,column-definition>...>

<MODIFY column-definition
<,column-definition>...>

<DROP column <,column>...>;

CREATE <UNIQUE> INDEX index-name

ON table-name (column <,column>...);

CREATE TABLE table-name (column-definition <,column-definition>...);

(column-specification , ...<constraint-specification > ,...) ;

CREATE TABLE table-name LIKE table-name;

CREATE TABLE table-name AS query-expression

<ORDER BY order-by-item <,order-by-item>...>;

CREATE VIEW proc-sql-view AS query-expression

<ORDER BY order-by-item <,order-by-item>...>;

<USING libname-clause<, libname-clause>...>;

DELETE

FROM table-name|proc-sql-view |sas/access-view <AS alias>

<WHERE sql-expression>;

DESCRIBE TABLEtable-name<,table-name>... ;

DESCRIBE TABLE CONSTRAINTS table-name <, table-name>... ;

DESCRIBE VIEW proc-sql-view <,proc-sql-view>... ;

DROP INDEX index-name <,index-name>...
FROM table-name;

DROP TABLE table-name <,table-name>...;

DROP VIEW view-name <,view-name>...;

INSERT INTO table-name|sas/access-view|proc-sql-view <(column<,column>...) >

SET column=sql-expression
<,column=sql-expression>...
<SET column=sql-expression
<,column=sql-expression>...>;

INSERT INTO table-name|sas/access-view|proc-sql-view<(column<,column>...)>

VALUES (value<,value>...)
<VALUES (value <,value>...)>...;

INSERT INTO table-name|sas/access-view|proc-sql-view
<(column<,column>...)> query-expression;

RESET <option(s)>;

SELECT <DISTINCT> object-item <,object-item>...

<INTO :macro-variable-specification
<, :macro-variable-specification>...>

FROM from-list

<WHERE sql-expression>

<GROUP BY group-by-item
<,group-by-item>...>

<HAVING sql-expression>

<ORDER BY order-by-item
<,order-by-item>...>;

UPDATE table-name|sas/access-view|proc-sql-view <AS alias>

SET column=sql-expression
<,column=sql-expression>...

<SETcolumn=sql-expression
<,column=sql-expression>...>

<WHERE sql-expression>;

VALIDATEquery-expression;

To connect to a DBMS and send it a DBMS-specific nonquery SQL statement, use this form:

PROC SQL;

<CONNECT TO dbms-name <AS alias><
<(connect-statement-argument-1=value
...<connect-statement-argument-n=value>)>>
<(dbms-argument-1=value
...<dbms-argument-n=value>)>>;

EXECUTE (dbms-SQL-statement)
BY dbms-name|alias;

<DISCONNECT FROM dbms-name|alias;>

<QUIT;>

To connect to a DBMS and query the DBMS data, use this form:

PROC SQL;

<CONNECT TO dbms-name <AS alias><
<(connect-statement-argument-1=value
...<connect-statement-argument-n=value>)>>
<(dbms-argument-1=value
...<dbms-argument-n=value>)>>;

SELECT column-list

FROM CONNECTION TO dbms-name|alias
(dbms-query)
optional PROC SQL clauses;

<DISCONNECT FROM dbms-name|alias;>

<QUIT;>

To do this Use this statement

Modify, add, or drop columns ALTER TABLE

Establish a connection with a DBMS
CONNECT

Create an index on a column CREATE INDEX

Create a PROC SQL table CREATE TABLE

Create a PROC SQL view CREATE VIEW

Delete rows DELETE

Display a definition of a table or view DESCRIBE

Terminate the connection with a DBMS DISCONNECT

Delete tables, views, or indexes DROP

Send a DBMS-specific nonquery SQL statement to a DBMS EXECUTE

Add rows INSERT

Reset options that affect the procedure environment without restarting the procedure RESET

Select and execute rows SELECT

Query a DBMS CONNECTION TO

Modify values UPDATE

Verify the accuracy of your query VALIDATE

Chapter Contents
Previous
Next
Top of Page

To do this		Use this statement
Modify, add, or drop columns		ALTER TABLE
Establish a connection with a DBMS		CONNECT
Create an index on a column		CREATE INDEX
Create a PROC SQL table		CREATE TABLE
Create a PROC SQL view		CREATE VIEW
Delete rows		DELETE
Display a definition of a table or view		DESCRIBE
Terminate the connection with a DBMS		DISCONNECT
Delete tables, views, or indexes		DROP
Send a DBMS-specific nonquery SQL statement to a DBMS		EXECUTE
Add rows		INSERT
Reset options that affect the procedure environment without restarting the procedure		RESET
Select and execute rows		SELECT
Query a DBMS		CONNECTION TO
Modify values		UPDATE
Verify the accuracy of your query		VALIDATE