BookmarkSubscribeRSS Feed
tom12122
Obsidian | Level 7

When I use PROC SQL; DELETE FROM TABLE; i see that sas file is constantly increasing. How can I enforce (with some option perhaps) to delete recordes permanently and free the disk space?

7 REPLIES 7
Haikuo
Onyx | Level 15

I am not sure if this is relevant: if I use system options compress=yes, and then after I delete some records, the file size remains the same, unless you invoke another system option: reuse=yes.

So it would look like this:

options compress=yes;

options reuse=yes;

What it does is to reuse those vacant slots from deleted records instead of appending to the end.

BTW, these two options are also data set options as well.

Regards,

Haikuo

Ksharp
Super User

I don't understand what you mean.

If you want delete a table ,then use DROP TABLE

If you want keep the constructure of a table ,then use CREAT TABLE xx LIKE xx, then drop the original table.

Ksharp

manojinpec
Obsidian | Level 7

Example of your query and what you are trying to achieve? Please elaborate

tom12122
Obsidian | Level 7

I've got table T1.

When I delete all record from it (Proc sql; Delete from T1;) the table size doesn't decrease. Moreover, when I put some records in this table I see that record number shows as If there were some "invisible records" inside. System seems to keep deleted record which take disk space and when iserting new records the table size is contantly growing..

manojinpec
Obsidian | Level 7

when you delete all records from table , try seeing contents of dataset and see if you can see that or not.

Ksharp
Super User

Then you tried the way HaiKuo proposed?

Tom
Super User Tom
Super User

You seem to have figured out how SAS works when you delete records without recreating the dataset.  If it is a concern to you then recreate the dataset.  (data old; set old; run;)

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
  • 7 replies
  • 2859 views
  • 2 likes
  • 5 in conversation