integrity constraints to an existing data set

Elfakhfakh — Fri, 16 Aug 2019 16:49:57 GMT

Hello all,

I am new to SAS and I am using SAS university edition and I am trying to set integrity constraints to an existing data set that has one observation violates one of the constraints. But the code does not complete because of this error ( ERROR: Integrity constraint Low_ck was rejected because 1 observations failed the constraint. ) please find the code below.

My goal from this code is to make sure that the data set meets all the constraints and if not, a way or a report identifying which observations don't meet them and which one. I can do it but I have to create an empty data set and append this data set to it then I can get the report by the audit file, can I do it without the empty data set method?

proc datasets library=pg1 nolist;
modify NYSE_test;
ic create Symbol_pk = primary key(Symbol)
message = "Symbol must be unique and non-missing"
msgtype = user;

ic create Open_ck = check(where=(Open is not missing))
message = "Open price must not missing"
msgtype = user;

ic create Close_ck = check(where=( Close between Low and High ))
message = "Close price must not missing or not between L and H"
msgtype = user;

ic create Low_ck = check(where=(Low is not missing))
message = "Low price must not missing"
msgtype = user;

ic create High_ck = check(where=(High is not missing))
message = "High price must not missing"
msgtype = user;

run;

audit NYSE_test;
initiate;
run;
quit;

title "Integrity Constraint Violations";
proc report data=pg1.NYSE_test(type=audit);
where _ATOPCODE_ in ('EA' 'ED' 'EU');
columns Symbol Date Open Low High Close Volume _ATMESSAGE_;
define  _atmessage_ / display "_Error Report_"
width=30 flow;
run;

thank you so much in advance,

Mohamed Elfakhfakh

Re: integrity constraints to an existing data set

VDD — Fri, 16 Aug 2019 18:33:56 GMT

you have to fix the record that doesn't meet you constrants.

ic create Low_ck = check(where=(Low is not missing))
message = "Low price must not missing"
msgtype = user;

you data has 1 record with missing low price.

topic integrity constraints to an existing data set in SAS Programming

integrity constraints to an existing data set

Re: integrity constraints to an existing data set