topic Proc SQL Deleting Records Space Utilization in New SAS User

Proc SQL Deleting Records Space Utilization

ssaha — Fri, 12 Apr 2019 08:34:58 GMT

Hi SAS Experts

When I am deleting records from a table with Proc Sql method, it is deleting the records but it is not reclaiming the space. Everytime I run the same code and inserting it again, It shows the actual number of rows but the space is increasing.

So can I reclaim the space with Proc SQL method and also why I am seeing this issue?

Re: Proc SQL Deleting Records Space Utilization

Kurt_Bremser — Fri, 12 Apr 2019 09:00:53 GMT

"Deleting" the way you do it only marks the observations as deleted. Physical space is still used, and will only be reused under special circumstances and options.

To reclaim the space, you have to rewrite the dataset as a whole.

Re: Proc SQL Deleting Records Space Utilization

LinusH — Sun, 14 Apr 2019 07:02:23 GMT

Another option is to compress the data set and using the REUSE option.
Over time your data set might get fragmented so still you might consider total rewrite as suggested by @Kurt_Bremser at a certain interval.