When generation data sets are used, you must create the integrity constraints in each data
set generation that includes protected variables.
CAUTION:
CHECK constraints in SAS 9.2 are not compatible with earlier releases of SAS.
If you add a CHECK constraint to an existing SAS data set or create a SAS data set
that includes a CHECK constraint, the data set cannot be accessed by a release prior
to SAS 9.2.
Specifying Physical Location for Inter-Libref Referential Integrity
Constraints When Sharing Disk Space
When you share disk space over a network and access referential integrity constraints in
which the foreign key and primary key data files are in different SAS libraries, a
standard should be established for the physical location of the shared files. A standard is
required when you create the shared files so that network machines use the same
physical name in order to access the files. If the physical names do not match, SAS
cannot open the referenced foreign key or primary key SAS data file.
For example, a standard might be established that all shared files are placed on disk T: so
that network machines use the same pathname in order to access the files.
Here is an example of a problem regarding files that were created without a standard.
Suppose a primary key and a foreign key SAS data file were created on machine D4064
in different directories C:Publicpkey_directory and C:Public
fkey_directory
. The pathnames are stored in the descriptor information of the SAS
data files.
To access the primary key data file from a different machine such as F2760, the
following LIBNAME statement would be executed:
libname pkds '\D4064Publicpkey_directory';
When the primary key data file is opened for update processing, SAS automatically tries
to open the foreign key data file by using the foreign key data file's physical name that is
stored in the primary key data file, which is C:Publicfkey_directory.
However, that directory does not exist on machine F2760. Therefore, opening the foreign
key data file fails.
Listing Integrity Constraints
PROC CONTENTS and PROC DATASETS report integrity constraint information
without special options. In addition, you can print information about integrity constraints
and indexes to a data set by using the OUT2= option. In PROC SQL, the DESCRIBE
TABLE and DESCRIBE TABLE CONSTRAINTS statements report integrity constraint
characteristics as part of the data file definition or alone, respectively. SCL provides the
ICTYPE, ICVALUE, and ICDESCRIBE functions for getting information about
integrity constraints. For more information see Base SAS Procedures Guide and SAS
Component Language: Reference for more information.
Rejected Observations
You can customize the error message that is associated with an integrity constraint when
you create the constraint by using the MESSAGE= and MSGTYPE= options. The
MESSAGE= option enables you to prepend a user-defined message to the SAS error
message associated with an integrity constraint. The MSGTYPE= option enables you to
Understanding Integrity Constraints 631
suppress the SAS portion of the message. For more information, see the PROC
DATASETS, PROC SQL, and SCL documentation.
Rejected observations can be collected in a special file by using an audit trail.
Integrity Constraints and CEDA Processing
When a SAS data file requires processing with CEDA, integrity constraints are not
supported. For example, if you transfer a SAS data file with defined integrity constraints
from one operating environment such as Windows to a different operating environment
such as UNIX, CEDA translates the file for you, but the integrity constraints are not
available. For information about CEDA processing, see Chapter 32, “Processing Data
Using Cross-Environment Data Access (CEDA),” on page 709.
The MIGRATE procedure preserves integrity constraints when migrating data files. For
more information, see the “MIGRATE” in Base SAS Procedures Guide. The CPORT and
CIMPORT procedures preserve integrity constraints when transporting SAS data files
from one operating environment to another operating environment. The CPORT
procedure makes a copy of the data file in a transportable format. The CIMPORT
procedure reads the transport file and creates a new host-specific copy of the data file.
For more information, see the “CPORT” in Base SAS Procedures Guide and
“CIMPORT” in Base SAS Procedures Guide.
Examples
Creating Integrity Constraints with the DATASETS Procedure
The following sample code creates integrity constraints by means of the DATASETS
procedure. The data file TV_Survey checks the percentage of viewing time spent on
networks, PBS, and other channels, with the following integrity constraints:
• The viewership percentage cannot exceed 100%.
• Only adults can participate in the survey.
• Gender can be male or female.
data tv_survey(label='Validity checking');
length idnum age 4 gender $1;
input idnum gender age network pbs other;
datalines;
1 M 55 80 . 20
2 F 36 50 40 10
3 M 42 20 5 75
4 F 18 30 0 70
5 F 84 0 100 0
;
proc datasets nolist;
modify tv_survey;
ic create val_gender = check(where=(gender in ('M','F')))
message = "Valid values for variable GENDER are
either 'M' or 'F'.";
ic create val_age = check(where=(age >= 18 and age = 120))
message = "An invalid AGE has been provided.";
ic create val_new = check(where=(network = 100));
ic create val_pbs = check(where=(pbs = 100));
ic create val_ot = check(where=(other = 100));
632 Chapter 26 • SAS Data Files
ic create val_max = check(where=((network+pbs+other)= 100));
quit;
Creating Integrity Constraints with the SQL Procedure
The following sample program creates integrity constraints by means of the SQL
procedure. The data file People lists employees and contains employment information.
The data file Salary contains salary and bonus information. The integrity constraints are
as follows:
• The names of employees receiving bonuses must be found in the People data file.
• The names identified in the primary key must be unique.
• Gender can be male or female.
• Job status can be permanent, temporary, or terminated.
proc sql;
create table people
(
name char(14),
gender char(6),
hired num,
jobtype char(1) not null,
status char(10),
constraint prim_key primary key(name),
constraint gender check(gender in ('male' 'female')),
constraint status check(status in ('permanent'
'temporary' 'terminated'))
);
create table salary
(
name char(14),
salary num not null,
bonus num,
constraint for_key foreign key(name) references people
on delete restrict on update set null
);
quit;
Creating Integrity Constraints By Using SCL
To add integrity constraints to a data file by using SCL, you must create and build an
SCL catalog entry. The following sample program creates and compiles catalog entry
Example.Ic_Cat_Allics.SCL.
INIT:
put "Test SCL integrity constraint functions start.";
return;
MAIN:
put "Opening WORK.ONE in utility mode.";
dsid = open('work.one', 'V');/* Utility mode.*/
if (dsid = 0) then
do;
_msg_=sysmsg();
Understanding Integrity Constraints 633
put _msg_=;
end;
else do;
if (dsid > 0) then
put "Successfully opened WORK.ONE in"
"UTILITY mode.";
end;
put "Create a check integrity constraint named teen.";
rc = iccreate(dsid, 'teen', 'check',
'(age > 12) && (age < 20)');
if (rc > 0) then
do;
put rc=;
_msg_=sysmsg();
put _msg_=;
end;
else do;
put "Successfully created a check"
"integrity constraint.";
end;
put "Create a not-null integrity constraint named nn.";
rc = iccreate(dsid, 'nn', 'not-null', 'age');
if (rc > 0) then
do;
put rc=;
_msg_=sysmsg();
put _msg_=;
end;
else do;
put "Successfully created a not-null"
"integrity constraint.";
end;
put "Create a unique integrity constraint named uq.";
rc = iccreate(dsid, 'uq', 'unique', 'age');
if (rc > 0) then
do;
put rc=;
_msg_=sysmsg();
put _msg_=;
end;
else do;
put "Successfully created a unique"
"integrity constraint.";
end;
put "Create a primary key integrity constraint named pk.";
rc = iccreate(dsid, 'pk', 'Primary', 'name');
if (rc > 0) then
do;
634 Chapter 26 • SAS Data Files
put rc=;
_msg_=sysmsg();
put _msg_=;
end;
else do;
put "Successfully created a primary key"
"integrity constraint.";
end;
put "Closing WORK.ONE.";
rc = close(dsid);
if (rc > 0) then
do;
put rc=;
_msg_=sysmsg();
put _msg_=;
end;
put "Opening WORK.TWO in utility mode.";
dsid2 = open('work.two', 'V');
/*Utility mode */
if (dsid2 = 0) then
do;
_msg_=sysmsg();
put _msg_=;
end;
else do;
if (dsid2 > 0) then
put "Successfully opened WORK.TWO in"
"UTILITY mode.";
end;
put "Create a foreign key integrity constraint named fk.";
rc = iccreate(dsid2, 'fk', 'foreign', 'name',
'work.one','null', 'restrict');
if (rc > 0) then
do;
put rc=;
_msg_=sysmsg();
put _msg_=;
end;
else do;
put "Successfully created a foreign key"
"integrity constraint.";
end;
put "Closing WORK.TWO.";
rc = close(dsid2);
if (rc > 0) then
do;
put rc=;
_msg_=sysmsg();
put _msg_=;
end;
return;
Understanding Integrity Constraints 635
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.