is one of the following:
specifies that all rows in table-name satisfy the WHERE-clause .
specifies that the values of the column must be unique. This constraint is identical to UNIQUE.
specifies that the column does not contain a null or missing value, including special missing values.
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. |
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.
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 |
| _CK xxxx _ | Check |
| _FK xxxx _ | Foreign key |
| _NM xxxx _ | Not Null |
| _PK xxxx _ | Primary key |
| _UN xxxx _ | Unique |
where xxxx is a counter that begins at 0001.
is one of the following:
specifies that all rows in table-name satisfy the WHERE-clause .
specifies that the values of each column must be unique. This constraint is identical to UNIQUE.
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.
specifies that column does not contain a null or missing value, including special missing values.
PRIMARY KEY ( column )
specifies one or more primary key columns, that is, columns that do 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. |
specifies that the values of each column must be unique. This constraint is identical to DISTINCT.
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 .
| CONSTRAINT constraint-name constraint > |
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.
specifies how the error message is displayed in the SAS log when an integrity constraint is not met.
the text that is specified for MESSAGE= is displayed as well as the default error message for that integrity constraint.
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.
creates table-name from the results of a query. See query-expression.
specifies the type of action to be performed on all matching foreign key values.
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.
occurs only if there are matching foreign key values. This referential action is the default.
sets all matching foreign key values to NULL.
creates table-name with the same column names and column attributes as table-name2 , but with no rows.
specifies a SAS WHERE clause. Do not include the WHERE keyword in the WHERE clause.
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.
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;
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.
For more information on integrity constraints, see the section on SAS files in SAS Language Reference: Concepts .