topic Dealing with dataset DELROWS in Administration and Deployment

Dealing with dataset DELROWS

W1ndwaker — Mon, 10 Oct 2022 10:28:35 GMT

Im checking my datasets and a way to shrink them to retrieve some space, and i saw that after deleting millions of rows from some datasets using proc sql; delete; the datasets keeps the size on the server file, and i saw in the dictionary table that there is column named delrows where you have all the data deleted "logically" from the dataset, but not from the file:

this is a sample of some tables with a lot of rows that are deleted from the dataset but keep, i supose logically in there, like a windows delete i supose there would be a "defragmentation" of the table to phisically delete this registers, but i didn't found anything on the kb to proceed with it, is there a way to permanently remove this rows and regain the space at administration level?

Or am i forced to do a DATA / SET step to recreate the table?

Thanks!

Re: Dealing with dataset DELROWS

LinusH — Mon, 10 Oct 2022 11:12:21 GMT

For SAS datasets, you basically have two options:

- as you suggested, recreate the dataset

- have the dataset compressed, and specify REUSE=YES. This will compact the dataset over time, but it will be fragmented as time goes by.

Re: Dealing with dataset DELROWS

W1ndwaker — Mon, 24 Oct 2022 14:49:49 GMT

I've been searching for other options but in the end i think we'll have to stick to the data step to recreate the table, and try to avoid deletes with proc sql in the bigger tables.

Thanks for the reply!

Re: Dealing with dataset DELROWS

Ksharp — Tue, 25 Oct 2022 11:58:05 GMT

/*
Do you have SAS/IML?
If you have, you could try PURGE operator

Here is an example:
*/


libname x v9 'C:\temp\a';
data x.have;
 set sashelp.heart;
run;

proc sql;
delete from x.have where status='Dead';
quit;



proc iml;
edit x.have;
purge;
close;
quit;

Re: Dealing with dataset DELROWS

W1ndwaker — Tue, 25 Oct 2022 12:41:00 GMT

I don't have IML, but in the end will be the same as a data step, because purge invalidates the index of the table and you have to recreate it, and this will lead to a pruge and then a index creation and will be the same probably speaking of mantainance times.

Think we will go for the data step, if it's run periodicaly it'll be pretty fast after the first celaning.