SAS Multiple Integrity Constraints Declaration PROC SQL

[Kiri_]

Hello,

Good day.
I've started studying about SAS Integrity Constrains. I'm currently experimenting with its code and syntax in SAS 9.4.
And I'm confused right now.

1) Why is this syntax valid? The log window didn't show any errors.

2) Is it ok to put multiple Integrity Constraints on a single variable column declaration?
     (below: 4 ICs are declared while declaring the variable Age, 2 of them doesn't concern with the Age variable.)

     (Also, out of the 4 ICs declared, only 1 did NOT work - which is "check (salary>10)". I tested it with other data values.)
3) Why did the row (name="Lorem Ipsum") got inserted into the dataset? His salary is $3, and there's an IC with "check (salary>10)" .

/*******************************************************************************/
proc sql feedback;
          create table test_1
                             (
                              constraint OK_name_1   not null (name) message="Name shouldn't be BLANK." msgtype=newline
                             ,constraint OK_prim_key primary key (company_id)
                             ,constraint OK_address  check (length(address)>1)

                             ,name  char(200)      label = "Name of Employee"  
                             ,age     date              label = "Age of Employee" distinct
                                                                                                        check (salary>10)
                                                                                                        check (length(name)>5)
                                                                                                        not null
                                                                                                        message="Age shouldn't be BLANK."
                                                                                                        msgtype=newline
                             ,address          varchar (200)  label = "Home Address"
                             ,company         char format=$200. length = 200 label = "Employer"
                             ,department     char format=$200. length = 200 label = "Department"
                             ,salary              num  format=dollar20.2   label = "Salary"
                             ,compensation num  format=dollar20.2  label = "Addt'l Compensation"
                             ,company_id    char format=$200. length = 200 label = "Company ID"
                             )
                ;
quit;

proc sql feedback;
                insert into test_1
                      values("Lorem Ipsum", 23, "New York City", "Vita Sparx", "Human Resource", 3, 10000,"LI001")
                ;
quit;

/*******************************************************************************/

Thanks in advance!

[yabwon · Accepted solution]

Hi, 

Please look at the following code (it is simpler version of yours):

proc sql feedback;
create table test
(
num_var num "a numerical variable" 
DISTINCT
NOT NULL 
UNIQUE
check (num_var>1) /* first  */
check (num_var>2) /* second */
check (num_var>3) /* third  */
)
;
quit;

proc contents data=test;
run;

When you look at proc contents results you will see that only one check was created, the first declaration was overwritten by second and second by third.

       Alphabetic List of Integrity Constraints

     Integrity                              Where
#    Constraint    Type        Variables    Clause

1    _CK0001_      Check                    num_var>3
2    _NM0001_      Not Null    num_var
3    _UN0001_      Unique      num_var

So in fact your code creates only 3 constraints, and since the "check (salary>10)" was not created that is why row (name="Lorem Ipsum") was inserted.

If I were you I would change code to something like that:

proc sql feedback;
create table test2
(
num_var num "a numerical variable" 
DISTINCT
NOT NULL 
UNIQUE
,constraint ch1 check (num_var>1) /* first  */
,constraint ch2 check (num_var>2) /* second */
,constraint ch3 check (num_var>3) /* third  */
)
;
quit;

proc contents data=test2;
run;

So not only you have all checks created by also you have full control with constraints naming.

And by the way you can also create/add/modify constraints with Proc Datasets IC CREATE.

All the best

Bart

[Kiri_]

Hi,

Thank you very much for the reply!

That helped a lot.

I never thought of using proc contents to see what actual ICs were in effect.

I didn't think SAS ICs can get overwritten.

Respectfully,

Kiri