The DATASETS Procedure

IC CREATE Statement

Creates an integrity constraint.

Restriction: Must be in a MODIFY RUN group

IC CREATE <constraint-name>=constraint <MESSAGE='message-string'>;

Required Arguments

<constraint-name=>

is a name for the constraint. The name must be a valid SAS name. When you do not supply a constraint name, a default name is generated. This default constraint name has the following form

Default name Constraint type

_NMxxxx_ Not Null

_UNxxxx_ Unique

_CKxxxx_ Check

_PKxxxx_ Primary key

_FKxxxx_ Foreign key

where xxxx is a counter beginning at 0001.

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

constraint

is the type of constraint. Valid values are

NOT NULL(variable)

specifies that variable does not contain a SAS missing value, including special missing values.

UNIQUE(variables)

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

DISTINCT(variables)

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

CHECK(WHERE-clause)

specifies validity checking with respect to lists and ranges. This is accomplished with a WHERE clause.

PRIMARY KEY(variable)

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

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

specifies a foreign key, that is, a set of variables whose values are linked to the values of the primary key variable in another data set. The referential actions are performed when updates are made to the values of a primary key variable that is referenced by a foreign key. For a RESTRICT referential action,

a delete operation: deletes the primary key row, but only if no foreign key values matches the deleted value.
an update operation: updates the primary key value, but only if no foreign keys match the current value to be updated.

For a SET NULL referential action,

a delete operation: deletes the primary key row and sets the corresponding foreign key values to NULL.
an update operation: modifies the primary key value and sets all matching foreign key values to NULL.

MESSAGE='message-string'

"message-string" is the text of an error message that is written to the log when the data fail the constraint. For example,

ic create not null(socsec) 
   message='Invalid Social Security number';

Length:

The maximum length of the message is 250 characters.

The following examples show how to create integrity constraints:

ic create a = not null(x);
ic create Unique_D = unique(d);
ic create Distinct_DE = distinct(d e);
ic create E_less_D = check(where (e < d or d = 99));
ic create primkey = primary key(a);
ic create not null (x);

Chapter Contents
Previous
Next
Top of Page

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