topic Re: Can I truncate a dataset preserving integrity constraints? in SAS Programming

Can I truncate a dataset preserving integrity constraints?

ScottBass — Thu, 07 Nov 2013 05:36:31 GMT

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

Re: Can I truncate a dataset preserving integrity constraints?

LinusH — Thu, 07 Nov 2013 08:29:59 GMT

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;

Re: Can I truncate a dataset preserving integrity constraints?

BrunoMueller — Thu, 07 Nov 2013 10:32:57 GMT

Hi Scott

Have a look at the code below. As mentioned all the integrity constraints are copied, but not the audit information.

options obs=0; 
proc datasets lib=work;
  append base=_class data=class;
  exchange class = _class;
  delete _class;
run; 
quit; 
options obs=max;

Re: Can I truncate a dataset preserving integrity constraints?

ScottBass — Fri, 22 Nov 2013 06:01:02 GMT

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

Re: Can I truncate a dataset preserving integrity constraints?

Peter_C — Fri, 22 Nov 2013 11:19:26 GMT

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.