I normally don't bother with constraints for SAS tables not the least because when loading into such a table and there is a violation then the record gets rejected but not even a warning condition gets created.
There was in the last years one occasion where I did bother because the integrity constraints where part of the input data model of a SAS solution.
In our case the input data model were SAS tables but they could also have been DB tables where constraints do make sense (like a PK constraint).
The solution provided a DDL to create the tables.
The approach I've taken:
1. Execute the solution DDL to create empty table structures
2. Using Proc Datasets add an audit trail to the tables to capture any rejected records
3. Load the tables (proc append).
4. If any data in the error audit trail export to an error table (err_<table name>)
5. Delete the audit trail (=restore table back exactly as per original DDL)
6. Check if there are any error tables (err_<table name>).
- if error tables found create error report and stop further processing
- if no error tables found move data tables to solution input area
Above approach sounds more complicated than it was to design and implement (using some dynamic code of course).
The advantage of above approach was that I didn't have to write code to check the data prior to loading and that any changes to the DDL (i.e. added column in later versions with more constraints like NOT NULL) get automatically reflected.
... View more