topic Proc SQL Delete From - Disk Space in SAS Programming

Proc SQL Delete From - Disk Space

newbi — Wed, 23 Apr 2014 18:09:07 GMT

How can I free up the disk space after deleting the data from table? I used Proc SQL - Delete From Statement to delete data from table. However, table size is not reduce.

Thanks

Re: Proc SQL Delete From - Disk Space

stat_sas — Wed, 23 Apr 2014 19:16:56 GMT

In oracle, When we DELETE rows from the table, all the data get copied into the Rollback Tablespace first then delete operation is performed. ROLLBACK after deleting a table, can bring back deleted data and a COMMIT can remove the rows permanently from the table and this causes to reduce size of table. I would recommend use pure SAS for this.

data have;

set have (obs=0);

run;

Insert rows which need to be retained in have from the main table and drop the main table. In that you will have a table occupying less space.

Re: Proc SQL Delete From - Disk Space

skillman — Wed, 23 Apr 2014 19:19:31 GMT

use a datastep instead:

data lib.table_name;

set lib.table_name (obs=0);

run;

Re: Proc SQL Delete From - Disk Space

LinusH — Wed, 23 Apr 2014 21:32:16 GMT

SAS is great for many things, but data management on a row level isn't one of them.

Deletion of single records will only mark them in the data set as logically deleted. Therefore you won't see any change in file size.

However, if you have compressed tables, you can also chose to have a dataset attribute (option) reuse=yes, this will save disk space over time. The risk with this method is that the internal structure of the data set will be cluttered, and could decrease performance.

So, if you have the possibility to rewrite the whole table, it's the cleanest solution.

If you have some data set attributes you wish to keep, such as indexes and constraints, they will be lost using techniques mentioned above. An easy way to prevent this is bt using proc apend (deleted is a temporary table):

proc append data=lib.have(where=(your subset criteria)) base=lib.deleted;

run;

proc datasets lib=lib nolist;

delete have;

run;

rename deleted=have;

run;

quit;

Re: Proc SQL Delete From - Disk Space

newbi — Fri, 25 Apr 2014 18:40:27 GMT

Thank you all for the response. I have resolve the issue by creating new dataeset.

Thanks