SAS/ACCESS Software for Relational Databases: Reference

SQL Procedure Syntax

PROC SQL <option(s)>;

ALTER TABLE SQL-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 SQL-view-name AS query-expression

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

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

DELETE

FROM table-name|sas/access-view|SQL-view-name <ASalias>

<WHEREsql-expression>;

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

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

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

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

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

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

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

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

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

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

INSERT INTO table-name|sas/access-view|SQL-view-name
<(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|view-name <AS alias>

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

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

<WHERE sql-expression>;

VALIDATEquery-expression;

Complete PROC SQL syntax information can be found in the SAS Procedures Guide.

Accomplished Task with PROC SQL
To do this Use this statement

Modify, add, or drop columns ALTER TABLE*

Establish a connection with a DBMS
LIBNAME, CONNECT

Create an index on a column CREATE INDEX*

Create a table CREATE TABLE

Create a PROC SQL view CREATE VIEW*

Delete rows from a table or view (does not apply to DATA step views) DELETE

Display a definition of a table or view DESCRIBE

Terminate the connection with a DBMS LIBNAME CLEAR, DISCONNECT

Delete tables, views, or indexes DROP

Add rows to a table or view (does not apply to DATA step views) INSERT

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

Select rows from a table SELECT

Modify values UPDATE

Verify the accuracy of your query VALIDATE*

*Does not apply to DBMS tables. To add a column to a DBMS table, you use the DBMS-specific SQL ALTER statement.

***Accomplished Task with PROC SQL***
To do this	Use this statement
Modify, add, or drop columns	ALTER TABLE*
Establish a connection with a DBMS	LIBNAME, CONNECT
Create an index on a column	CREATE INDEX*
Create a table	CREATE TABLE
Create a PROC SQL view	CREATE VIEW*
Delete rows from a table or view (does not apply to DATA step views)	DELETE
Display a definition of a table or view	DESCRIBE
Terminate the connection with a DBMS	LIBNAME CLEAR, DISCONNECT
Delete tables, views, or indexes	DROP
Add rows to a table or view (does not apply to DATA step views)	INSERT
Reset options that affect the procedure environment without restarting the procedure	RESET
Select rows from a table	SELECT
Modify values	UPDATE
Verify the accuracy of your query	VALIDATE*
*Does not apply to DBMS tables. To add a column to a DBMS table, you use the DBMS-specific SQL ALTER statement.

Chapter Contents
Previous
Next
Top of Page