topic Delete table in SAS Programming

Delete table

deleted_user — Wed, 14 Jul 2010 17:14:12 GMT

Hello,

I have a macro that applies a DELETE * to several SAS tables, but the space in disk doesnt change, how can I really delete the records from the table, instead of marking them as deleted.

Thanks in advance.

Re: Delete table

ArtC — Wed, 14 Jul 2010 17:37:40 GMT

It would be helpful to know how you are deleting the observations. FSEDIT? IML?
As a general rule observations marked for deletion are removed the next time the data set is reprocessed. Such as with a PROC SORT. In IML you can use the PURGE statement.

Re: Delete table

Peter_C — Thu, 15 Jul 2010 15:27:44 GMT

the sql delete statement is good where the objective is conditional deletion subject to a where clause. DELETE * implies all rows, a bit like TRUNCATE.
A SAS equivalent to TRUNCATE might work with this PROC DATASETS combination [pre]option _last_ = ;

%let your_lib = %scan( WORK.&syslast, -2, . ) ;
%let your_mem = %scan( &syslast, -1, . ) ;

proc datasets library= &your_lib nolist ;
delete newTab / mtype= data ; * just in case it already exists ;
run ;
* now copy required structure to the new entry ;
append base=newtab data= &your_mem(obs=0) ;
run ;
* next remove old data ;
delete &your_mem / mtype= data ;
run ;
* finally rename temporary structure with original member name ;
change newtab=&your_mem ;
run ;
quit ;[/pre]
Append is used because it will copy all indexes and integrity constraint definitions as well as column definitions, and it uses "copy" when the base= table doesn't exist.

Re: Delete table

Patrick — Fri, 16 Jul 2010 10:47:05 GMT

Hi Peter

Nice code which preserves all indexes and integrity constraints!
This mimics something like a REORG statement in PROC DATASETS which I hope SAS R&D will give us one day.

I've run into two minor issues while running your code over a sample dataset:
1: %let your_lib = %scan( WORK.&syslast, -2, . ) ;
I believe this should be without the 'WORK.'

2: append base=newtab data= &your_mem(obs=0) ;
'obs=0' results in deletion of all records, also the ones which are not marked as deleted.

Else: Everything run like a charm.

Thanks
Patrick

Re: Delete table

Peter_C — Fri, 16 Jul 2010 12:05:13 GMT

Patrick
re: . . obs=0
I wasn't expecting to do a REORG for this poster. I assumed he wanted to truncate the table with DELETE * and release the deleted space - i.e. not just mark for deletion ...
> I have a macro that applies a DELETE * to several SAS
> tables, but the space in disk doesnt change, how can
> I really delete the records from the table, instead
> of marking them as deleted.
Of course, you may be right that I misunderstood the objective.

As to that trick with WORK.
Should the syntax be embedded in a macro, I would expect the parameter passed to be a one or two level name.
From that &name %scan( &name, -1, . ) always returns the memname, but %scan( &name, -2, . ) won't return the libname if &name is just a one-level name (implying the work library). Hence %scan( WORK.&name, -2, . ) should find WORK where there is no "." in &name.
Of course, it might catch out my accustomed approach in development where I use the user option or lib-ref to catch all one-level names in a permanent dataset 😉