The SQL Procedure

CREATE TABLE Statement

Creates PROC SQL tables.

Featured in:

Creating a Table and Inserting Data into It

Creating a Table from a Query's Result

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

Arguments

column-constraint

is one of the following:

CHECK (WHERE-clause)

specifies that all rows in table-name satisfy the WHERE-clause.

DISTINCT

specifies that the values of the column must be unique. This constraint is identical to UNIQUE.

NOT NULL

specifies that the column does not contain a null or missing value, including special missing values.

PRIMARY KEY

specifies that the column is a primary key column, that is, a column that does not contain missing values and whose values are unique.

Restriction:

When defining overlapping primary key and foreign key constraints, the variables in a data file are part of both a primary key and a foreign key definition. If you use the exact same variables, then the variables must be defined in a different order.

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

specifies that the column is a foreign key, that is, a column whose values are linked to the values of the primary key variable in another table (the table-name that is specified for REFERENCES). The referential-actions are performed when the values of a primary key column that is referenced by the foreign key are updated or deleted.

Restriction:

When you are defining overlapping primary key and foreign key constraints, the variables in a data file are part of both a primary key definition and a foreign key definition. The restrictions are as follows:

If you use the exact same variables, then the variables must be defined in a different order.
The foreign key's update and delete referential actions must both be RESTRICT.

UNIQUE

specifies that the values of the column must be unique. This constraint is identical to DISTINCT.

Note: If you specify column-constraint, then SAS automatically assigns a name to the constraint. The constraint name has the form

Default name	Constraint type
_CKxxxx_	Check
_FKxxxx_	Foreign key
_NMxxxx_	Not Null
_PKxxxx_	Primary key
_UNxxxx_	Unique

where xxxx is a counter that begins at 0001. [cautionend]

column-definition

See column-definition.

column-specification

consists of

column-definition <column-constraint>

constraint

is one of the following:

CHECK (WHERE-clause)

specifies that all rows in table-name satisfy the WHERE-clause.

DISTINCT (column<, ... column>)

specifies that the values of each column must be unique. This constraint is identical to UNIQUE.

FOREIGN KEY (column<, ... column>) REFERENCES table-name <ON DELETE referential-action > <ON UPDATE referential-action>

specifies a foreign key, that is, a set of columns whose values are linked to the values of the primary key variable in another table (the table-name that is specified for REFERENCES). The referential-actions are performed when the values of a primary key column that is referenced by the foreign key are updated or deleted.

Restriction:

If you use the exact same variables, then the variables must be defined in a different order.
The foreign key's update and delete referential actions must both be RESTRICT.

NOT NULL (column)

specifies that column does not contain a null or missing value, including special missing values.

PRIMARY KEY (column<, ... column>)

specifies one or more primary key columns, that is, columns that do not contain missing values and whose values are unique.

Restriction:

UNIQUE (column<, ...column>)

specifies that the values of each column must be unique. This constraint is identical to DISTINCT.

constraint-name

specifies a name for the constraint that is being specified. The name must be a valid SAS name.

Note: The names PRIMARY, FOREIGN, MESSAGE, UNIQUE, DISTINCT, CHECK, and NOT cannot be used as values for constraint-name. [cautionend]

constraint-specification

consists of

CONSTRAINT constraint-name constraint <MESSAGE='message-string' <MSGTYPE=message-type>>

message-string

specifies the text of an error message that is written to the log when the integrity constraint is not met. The maximum length of message-string is 250 characters.

message-type

specifies how the error message is displayed in the SAS log when an integrity constraint is not met.

NEWLINE: the text that is specified for MESSAGE= is displayed as well as the default error message for that integrity constraint.
USER: only the text that is specified for MESSAGE= is displayed.

ORDER BY order-by-item

sorts the rows in table-name by the values of each order-by-item. See ORDER BY Clause.

query-expression

creates table-name from the results of a query. See query-expression.

referential-action

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

CASCADE: allows primary key data values to be updated, and updates matching values in the foreign key to the same values. This referential action is currently supported for updates only.
RESTRICT: occurs only if there are matching foreign key values. This referential action is the default.
SET NULL: sets all matching foreign key values to NULL.

table-name

in the CREATE TABLE statement, refers to the name of the table that is to be created. You can use data set options by placing them in parentheses immediately after table-name. See Using SAS Data Set Options with PROC SQL in SAS 9.2 SQL Procedure User's Guide for details.
in the REFERENCES clause, refers to the name of table that contains the primary key that is referenced by the foreign key.

table-name2

creates table-name with the same column names and column attributes as table-name2, but with no rows.

WHERE-clause

specifies a SAS WHERE clause. Do not include the WHERE keyword in the WHERE clause.

Creating a Table without Rows

The first form of the CREATE TABLE statement creates tables that automatically map SQL data types to tables that are supported by SAS. 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 TABLE 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, then create a view from the query expression instead of a table. See CREATE VIEW Statement.

CAUTION:

Recursive table references can cause data integrity problems.

While it is possible to recursively reference the target table of a CREATE TABLE AS statement, doing so can cause data integrity problems and incorrect results. Constructions such as the following should be avoided:

proc sql;
   create table a as
      select var1, var2
      from a;

[cautionend]

Integrity Constraints

You can attach integrity constraints when you create a new table. To modify integrity constraints, use the ALTER TABLE statement.

The following interactions apply to integrity constraints when they are part of a column specification.

You cannot specify compound primary keys.
The check constraint that you specify in a column specification does not need to reference that same column in its WHERE clause.
You can specify more than one integrity constraint.
You can specify the MSGTYPE= and MESSAGE= options on a constraint.

For more information on integrity constraints, see the section on SAS files in SAS Language Reference: Concepts.

Top of Page