SQL Procedure
- Syntax
Procedure SyntaxPROC SQL StatementALTER TABLE StatementCONNECT StatementCREATE INDEX StatementCREATE TABLE StatementCREATE VIEW StatementDELETE StatementDESCRIBE StatementDISCONNECT StatementDROP StatementEXECUTE StatementINSERT StatementRESET StatementSELECT StatementUPDATE StatementVALIDATE Statement - Overview
- Examples Creating a Table and Inserting Data into ItCreating a Table from a Query's ResultUpdating Data in a PROC SQL TableJoining Two TablesCombining Two TablesReporting from DICTIONARY TablesPerforming an Outer JoinCreating a View from a Query's Result Joining Three TablesQuerying an In-Line View Retrieving Values with the SOUNDS-LIKE Operator Joining Two Tables and Calculating a New ValueProducing All the Possible Combinations of the Values in a ColumnMatching Case Rows and Control RowsCounting Missing Values with a SAS Macro
| 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;
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>)>;
<… VALUES (value <, … value>)>;
INSERT
INTO table-name|sas/access-view|proc-sql-view
<(column<, …column>)> query-expression;
<(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>)>;
<(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;
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>)>;
<(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;
(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 |
Copyright © SAS Institute Inc. All rights reserved.