DATA Step, Macro, Functions and more

Can I truncate a dataset preserving integrity constraints?

Accepted Solution Solved
Reply
Super Contributor
Posts: 377
Accepted Solution

Can I truncate a dataset preserving integrity constraints?

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


Accepted Solutions
Solution
‎11-07-2013 05:32 AM
SAS Super FREQ
Posts: 685

Re: Can I truncate a dataset preserving integrity constraints?

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;

View solution in original post


All Replies
Super User
Posts: 5,260

Re: Can I truncate a dataset preserving integrity constraints?

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;

Data never sleeps
Solution
‎11-07-2013 05:32 AM
SAS Super FREQ
Posts: 685

Re: Can I truncate a dataset preserving integrity constraints?

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;
Super Contributor
Posts: 377

Re: Can I truncate a dataset preserving integrity constraints?

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

Valued Guide
Posts: 2,175

Re: Can I truncate a dataset preserving integrity constraints?

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1732 views
  • 3 likes
  • 4 in conversation