topic Re: Difference in dataset size after updating the metadata in SAS Programming

Difference in dataset size after updating the metadata

Jagadeesh2907 — Wed, 02 Nov 2016 06:41:38 GMT

Hi ,

I had a 121 GB Dataset in which i had to change the length of a variable from char(20) to char(50). I used the below code to do this and found that the resulting dataset was only 23 GB. However when i did the proc contents i saw both records had the same number of observation and the only difference in the output of proc contents was that in the dataset before updating "Deleted Observation" value was 69956558 and after updating "Deleted Observation" value was 0. Could you please let me know what difference does this make and is this the reason for size change.

Code for updating variable size:

proc sql;
alter table mart.s2_pseudo_qrt_table
modify PRODUCT_LINE_ID char(50) ;
quit;

Re: Difference in dataset size after updating the metadata

SASKiwi — Wed, 02 Nov 2016 06:50:29 GMT

It looks like your dataset originally contained nearly 70M observations that were marked for deletion but still taking up space. When you did your ALTER TABLE the entire dataset was processed, removing the observations marked for deletion and the space they were taking up. This explains the drop in dataset size.

Re: Difference in dataset size after updating the metadata

Jagadeesh2907 — Wed, 02 Nov 2016 07:42:30 GMT

Hi , Thank you for the explanation. This gives a fair idea. However, when you say records were marked for deletion but were not actually deleted ?

Re: Difference in dataset size after updating the metadata

Kurt_Bremser — Wed, 02 Nov 2016 08:03:36 GMT

This can be achieved by (eg) a data step with modify and remove.

Re: Difference in dataset size after updating the metadata

Jagadeesh2907 — Thu, 03 Nov 2016 08:02:53 GMT

Can you please give an example of the same.

Re: Difference in dataset size after updating the metadata

Kurt_Bremser — Thu, 03 Nov 2016 08:35:56 GMT

@Jagadeesh2907 wrote:

Can you please give an example of the same.

See this very simple example:

data class;
set sashelp.class;
run;

data class;
modify class;
if age = 15 then remove;
run;

proc contents data=class;
run;

Partial result:

                                    The CONTENTS Procedure

Data Set Name        WORK.CLASS                                       Observations          15
Member Type          DATA                                             Variables             5 
Engine               V9                                               Indexes               0 
Created              Do, 03. Nov 2016 09.24 Uhr                       Observation Length    40
Last Modified        Do, 03. Nov 2016 09.24 Uhr                       Deleted Observations  4 
Protection                                                            Compressed            NO
Data Set Type                                                         Sorted                NO
Label

You can see that the original 19 obs of sashelp.class have been reduced to 15, but you also see 4 deleted observations that are still physically in there and consume space.

Now let's run a "simple" data step over that:

data class;
set class;
run;

proc contents data=class;
run;

New result:

                                     The CONTENTS Procedure

 Data Set Name        WORK.CLASS                                       Observations          15
 Member Type          DATA                                             Variables             5 
 Engine               V9                                               Indexes               0 
 Created              Do, 03. Nov 2016 09.32 Uhr                       Observation Length    40
 Last Modified        Do, 03. Nov 2016 09.32 Uhr                       Deleted Observations  0 
 Protection                                                            Compressed            NO
 Data Set Type                                                         Sorted                NO

Now those 4 deleted observations have vanished, and SAS can reduce the physical size of the dataset.

Of course, given the small size of SASHELP.CLASS, this will not be visible on the OS level, as only 1 dataset page is needed anyway. But you get the picture.

Re: Difference in dataset size after updating the metadata

Jagadeesh2907 — Tue, 11 Sep 2018 15:00:49 GMT

Thank you so much.