The SQL Procedure

CREATE TABLE Statement

Creates PROC SQL tables.

Featured in: Creating a Table and Inserting Data into It and Creating a Table from a Query's Result

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

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

where column-specification is

column-definition <column-attribute>

where constraint-specification is

CONSTRAINT constraint-name constraint

column-attribute is one of the following:

UNIQUE

DISTINCT [Note: This is a DB2 extension. DISTINCT is the same as UNIQUE.]

NOT NULL

CHECK ( WHERE-clause )

PRIMARY KEY

REFERENCES table-name
<ON DELETE referential-action > <ON UPDATE referential-action >

constraint is one of the following:

NOT NULL (column)

CHECK (WHERE-clause)

PRIMARY KEY (columns)

DISTINCT (columns)

UNIQUE (columns)

FOREIGN KEY (columns)
REFERENCES table-name
<ON DELETE referential-action> <ON UPDATE referential-action>

CREATE TABLEtable-name LIKE table-name;

CREATE TABLE table-name AS query-expression

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

Arguments

column-definition

See column-definition .

constraint-name

is the name for the constraint being specified.

order-by-item

See ORDER BY Clause .

query-expression

See query-expression .

referential-action

specifies the type of action to be performed on all matching foreign key values.

RESTRICT: occurs only if there are matching foreign key values. This is the default referential action.
SET NULL: sets all matching foreign key values to NULL.

table-name

is the name of the table containing the primary key referenced by the foreign key.

WHERE clause

specifies a SAS WHERE-clause.

Creating a Table without Rows

The first form of the CREATE TABLE statement creates tables that automatically map SQL data types to those supported by the SAS System. Use this form when you want to create a new table with columns that are not present in existing tables. It is also useful if you are running SQL statements from an SQL application in another SQL-based database.
The second form uses a LIKE clause to create a table that has the same column names and column attributes as another table. To drop any columns in the new table, you can specify the DROP= data set option in the CREATE TABLE statement. The specified columns are dropped when the table is created. Indexes are not copied to the new table.
Both of these forms create a table without rows. You can use an INSERT statement to add rows. Use an ALTER statement to modify column attributes or to add or drop columns.

Creating a Table from a Query Expression

The third form of the CREATE TABLE statement stores the results of any query-expression in a table and does not display the output. It is a convenient way to create temporary tables that are subsets or supersets of other tables.
When you use this form, a table is physically created as the statement is executed. The newly created table does not reflect subsequent changes in the underlying tables (in the query-expression). If you want to continually access the most current data, create a view from the query expression instead of a table. See CREATE VIEW Statement .

Integrity Constraints
You can attach integrity constraints when you create a new table. To modify integrity constraints, use the ALTER TABLE statement. For more information on integrity constraints, see the section on SAS files in SAS Language Reference: Concepts.

Chapter Contents
Previous
Next
Top of Page