Hi,
If I create a permanent dataset containing integrity constraints, but need to occasionally rebuild it from scratch, is it possible to truncate the dataset and preserve integrity constraints?
I know that an OUT2= dataset may allow me to kludge the recreation of the IC's; what I want to know is if I can truncate the dataset and maintain the ICs?
Sample code:
data class;
set sashelp.class;
run;
proc datasets lib=work nolist;
audit class;
initiate;
user_var reason_code $20;
run;
modify class;
ic create primary_key = primary key (name);
ic create val_gender = check(where=(sex in ('M','F')))
message = "Valid values for variable GENDER are either 'M' or 'F'."
msgtype=user;
ic create val_age = check(where=(age >= 10 and age <= 20))
message = "An invalid AGE has been provided."
msgtype=user;
run;
quit;
* none of these work... ;
data class;
if 0 then modify class;
stop;
run;
* works but would perform horribly on a large dataset ;
data class;
modify class;
remove;
run;
data class;
if 0 then set class;
run;
proc sql;
truncate table class; * invalid SQL ;
quit;
proc sql;
create table class as select * from class (obs=0);
quit;
* blind hacking at this point... ;
proc append base=class(obs=0) data=class(obs=0);
run;
Thanks,
Scott
Proc append with a non-existing base-table will give a copy all attributes of the data= table, including constraints.
proc append base=class_trunc data=class(obs=0);
run;
proc datasets lib=work;
...statements to drop original table and rename the truncated copy to original name...
quit;
Hi,
Another issue related to the original question...
Run the below code:
options ls=max;
* start with a "clean slate" ;
proc datasets lib=work nowarn nolist kill;
quit;
* create sample data (with write access) ;
* for added safety, we're also specifying an alter password ;
data class (alter=password);
set sashelp.class;
stop;
run;
* use proc datasets to create integrity constraints and control auditing ;
proc datasets lib=work nolist;
* turn on auditing ;
audit class (alter=password);
initiate;
* a user-specified user variable in the audit dataset ;
user_var reason_code $30;
run;
* create integrity constraints ;
modify class (alter=password);
ic create primary_key = primary key (name);
ic create val_gender = check(where=(sex in ('M','F')))
message = "Valid values for variable GENDER are either 'M' or 'F'."
msgtype=user;
ic create val_age = check(where=(age >= 10 and age <= 20))
message = "An invalid AGE has been provided."
msgtype=user;
run;
quit;
* create transaction dataset ;
data source;
set sashelp.class;
length reason_code $30;
reason_code="Adding new data";
run;
* append 5 observations ;
proc append base=class data=source (obs=5);
run;
* append all observations. the first 5 should be rejected by the integrity constraints ;
proc append base=class data=source;
run;
* note that class has logically deleted observations. ;
* pay close attention to the observation numbers (i.e. obs 6-10) ;
proc print data=class;
run;
It would have been nice if SAS could have detected the IC violation before attempting to add the record to the target dataset, rather than adding it and then logically deleting the added record after the fact.
Questions:
1) Is there a way to remove the logically deleted records and preserve the IC's and auditing?
2) In a "large" dataset with potentially thousands or millions of logically deleted observations, would that contribute to "file bloating" and reduced performance than if the logically deleted records were not present?
Thanks,
Scott
to remove that empty space from the table I think you need to recreate and the audit would be lost. Hiwever, you could extract the audit as a table before losing it.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.