BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ScottBass
Rhodochrosite | Level 12

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


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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

4 REPLIES 4
LinusH
Tourmaline | Level 20

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
BrunoMueller
SAS Super FREQ

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;
ScottBass
Rhodochrosite | Level 12

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


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
Peter_C
Rhodochrosite | Level 12

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 6682 views
  • 5 likes
  • 4 in conversation