Placing Integrity Constraints on a Data Set

Overview

Integrity constraints can be created using either the DATASETS procedure or the SQL procedure.
Although you can use either procedure to create integrity constraints on existing data sets, you must use PROC SQL if you want to create integrity constraints at the same time that you create the data set. In this chapter you learn to use PROC DATASETS to place integrity constraints on an existing data set.
General form, DATASETS procedure with the IC CREATE statement:
PROC DATASETS LIB=libref <NOLIST>;
MODIFY SAS-data-set;
IC CREATE constraint-name=constraint
<MESSAGE='Error Message'>;
QUIT;
Here is an explanation of the syntax:
libref
is the library in which the data set is stored. If you do not specify the LIB= option, the procedure uses the Work library.
NOLIST
suppresses the directory listing.
SAS-data-set
is the name of the data set to which you want to apply the integrity constraint.
constraint-name
is any name that you want to give the integrity constraint.
constraint
is the type of constraint that you are creating, specified in the following format:
  • NOT NULL (variable)
  • UNIQUE (variables)
  • CHECK (where-expression)
  • PRIMARY KEY (variables)
  • FOREIGN KEY (variables) REFERENCES table-name.
Error Message
is an optional message written to the log when the constraint is violated.
Note: You can use IC or INTEGRITY CONSTRAINT interchangeably.
Tip
To learn how to create integrity constraints using the SQL procedure, see Creating and Managing Tables Using PROC SQL.

Example

Suppose you have a data set that contains route information and passenger capacity for each class in an airline. You need to create integrity constraints to ensure that, when the data set is updated, the following conditions are true:
  • The route ID number is both unique and required (PRIMARY KEY).
  • The capacity for business class passengers must either be missing or be greater than the capacity for first class passengers (CHECK).
In the code below, the IC CREATE statement is used to create two general integrity constraints on variables in the data set Capinfo:
  • The PRIMARY KEY constraint is placed on the RouteID variable. This constraint ensures that when values of RouteID are updated, they must be unique and nonmissing.
    Note: The same effect could be achieved by applying both the UNIQUE and NOT NULL constraints, but the PRIMARY KEY constraint is used as a shortcut.
  • The CHECK constraint uses the WHERE expression to ensure that the only values of CapBusiness that are allowed are those greater than Cap1st or missing.
Note: If you choose to run this example, you must copy the data set Capinfo from the Sasuser library to the Work library. 
proc datasets nolist;
   modify capinfo;
   ic create PKIDInfo=primary key(routeid)
      message='You must supply a Route ID Number';
   ic create Class1=check(where=(cap1st<capbusiness or capbusiness=.))
      message='Cap1st must be less than CapBusiness';
quit;
Notice that the NOLIST option is used to prevent a listing of the Work library that PROC DATASETS produces by default. When the constraint is created, a message is written to the SAS log.
Table 18.1 SAS Log 
45 modify capinfo;
46 ic create PKIDInfo = primary key (routeid)
47 message = 'You must supply a Route ID Number';
NOTE: Integrity constraint PKIDInfo defined. 
48 ic create Class1 = check (where = (cap1st < capbusiness 
49 or capbusiness = .)) message = 'Cap1st must be less 
50 than CapBusiness'; 
NOTE: Integrity constraint Class1 defined. 
51 run;
Note: For the UNIQUE and PRIMARY KEY constraints, SAS builds indexes on the columns that are involved if an appropriate index does not already exist. Any index that is created by an integrity constraint can be used for other purposes, such as WHERE processing or the KEY= option in a SET statement.
Tip
For more information about creating integrity constraints, see the SAS documentation for the DATASETS procedure.

How Constraints Are Enforced

Once integrity constraints are in place, SAS enforces them whenever you modify the data set in place. Techniques for modifying data in place include using the following elements:
  • a DATA step with the MODIFY statement
  • interactive data editing windows
  • PROC SQL with the INSERT INTO, SET, or UPDATE statements
  • PROC APPEND

Example

The code in the previous example placed a check constraint on Cap1st and CapBusiness to ensure that values for the capacity in business class were either greater than first class or missing. Suppose you ran the following program to triple the capacity in first class. This would probably violate the check constraint for some observations. 
data capinfo;
   modify capinfo;
   cap1st=cap1st*3;
run;
The observations that failed to pass the integrity constraint are written to the SAS log. As you can see, all these observations would have had values of Cap1st greater than those of CapBusiness.
Table 18.2 SAS Log 
FlightID=IA00100 RouteID=0000001 Origin=RDU Dest=LHR Cap1st=42
CapBusiness=30 CapEcon=163
_ERROR_=1 _IORC_=660130 _N_=1
FlightID=IA00201 RouteID=0000002 Origin=LHR Dest=RDU Cap1st=42
CapBusiness=30 CapEcon=163
_ERROR_=1 _IORC_=660130 _N_=2
FlightID=IA00300 RouteID=0000003 Origin=RDU Dest=FRA Cap1st=42
CapBusiness=30 CapEcon=163
_ERROR_=1 _IORC_=660130 _N_=3
FlightID=IA00400 RouteID=0000004 Origin=FRA Dest=RDU Cap1st=42
CapBusiness=30 CapEcon=163
_ERROR_=1 _IORC_=660130 _N_=4
FlightID=IA02900 RouteID=0000029 Origin=SFO Dest=HNL Cap1st=42
CapBusiness=30 CapEcon=163
_ERROR_=1 _IORC_=660130 _N_=29
FlightID=IA03000 RouteID=0000030 Origin=HNL Dest=SFO Cap1st=42
CapBusiness=30 CapEcon=163
_ERROR_=1 _IORC_=660130 _N_=30
FlightID=IA03300 RouteID=0000033 Origin=RDU Dest=ANC Cap1st=42
CapBusiness=30 CapEcon=163
_ERROR_=1 _IORC_=660130 _N_=33
FlightID=IA03400 RouteID=0000034 Origin=ANC Dest=RDU Cap1st=42
CapBusiness=30 CapEcon=163
_ERROR_=1 _IORC_=660130 _N_=34
NOTE: There were 50 observations read from the data set WORK.CAPINFO.
NOTE:  The data set WORK.CAPINFO has been updated. There were 42
      observations rewritten, 0 observations added and 0 
      observations deleted.
NOTE: There were 8 rejected updates, 0 rejected adds, and 0 rejected
      deletes.
If you used the VIEWTABLE window or another interactive window to make this update, SAS displays the error message that is defined for the integrity constraint.
Note: Rejected observations can be collected in a special file using the audit trail functionality that you learn about later in this chapter.

Copying a Data Set and Preserving Integrity Constraints

The APPEND, COPY, CPORT, CIMPORT, and SORT procedures preserve integrity constraints when their operation results in a copy of the original data file. Integrity constraints are also preserved if you copy a data set using the SAS Explorer window.
Tip
For more information about preserving integrity constraints, see the SAS documentation.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.22.216.254