BookmarkSubscribeRSS Feed
newbi
SAS Employee

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

4 REPLIES 4
stat_sas
Ammonite | Level 13

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.

skillman
SAS Employee

use a datastep instead:

data lib.table_name;

set lib.table_name (obs=0);

run;

LinusH
Tourmaline | Level 20

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;

Data never sleeps
newbi
SAS Employee

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

Thanks

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
  • 3817 views
  • 1 like
  • 4 in conversation