The SQL Procedure

Syntax: SQL Procedure

Tip:

Supports the Output Delivery System. See Output Delivery System: Basic Concepts in SAS Output Delivery System: User's Guide for details.

Tip:

You can use any global statements. See Fundamental Concepts for Using Base SAS Procedures for a list.

Tip:

You can use data set options any time a table name or view name is specified. See Using SAS Data Set Options with PROC SQL in SAS 9.2 SQL Procedure User's Guide for details.

Tip:

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

view-name indicates a SAS view of any type.

Table of Contents:

The SQL Procedure

PROC SQL <option(s)>;

ALTER TABLE table-name

<ADD <CONSTRAINT> constraint-clause<, ... constraint-clause>>

;

CREATE <UNIQUE> INDEX index-name

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

CREATE TABLE table-name

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

;

CREATE TABLE table-name LIKE table-name2;

CREATE TABLE table-name AS query-expression

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

CREATE VIEW proc-sql-view AS query-expression

<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 VIEW proc-sql-view <, ... proc-sql-view>;

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

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>

FROM from-list

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

<WHERE sql-expression>;

VALIDATE query-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>)>
<(database-connection-argument-1=value <... database-connection-argument-n=value>)>;

EXECUTE (dbms-SQL-statement)
BY 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>)>
<(database-connection-argument-1=value <... database-connection-argument-n=value>)>;

SELECT column-list

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

<QUIT;>

Task		Statement
Create, maintain, retrieve, and update data in tables and views that are based on these tables		PROC SQL
Modify, add, or drop columns		ALTER TABLE
Establish a connection with a DBMS		CONNECT TO
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 FROM
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

Top of Page