SQL Procedure

Tips: Supports the Output Delivery System. For more information, see Chapter 3, “Output Delivery System: Basic Concepts,” in SAS Output Delivery System: User's Guide.
You can use any global statements. For more information, see Chapter 2, “Fundamental Concepts for Using Base SAS Procedures,” in Base SAS Procedures Guide.
You can use data set options any time a table name or view name is specified. For more information, see Using SAS Data Set Options with PROC SQL..
Regular type indicates the name of a component that is described in SQL Procedure Componentsview-name indicates a SAS view of any type.

Syntax

PROC SQL <option(s)>;
ALTER TABLE table-name
<ADD <CONSTRAINT> constraint-clause<, … constraint-clause>>
<ADD column-definition<, … column-definition>>
<DROP CONSTRAINT constraint-name <, … constraint-name>>
<DROP column<, … column>>
<DROP FOREIGN KEY constraint-name>
<DROP PRIMARY KEY>
<MODIFY column-definition<, … column-definition>>
;
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
<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 TABLE table-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>
< 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>
< 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;
<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>)>
<(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;
<DISCONNECT FROM dbms-name|alias;>
<QUIT;>

Table of Procedure Tasks

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