topic Re: Unexpected Size of Dataset in SAS Data Management

Unexpected Size of Dataset

agoldma — Sun, 26 May 2019 17:48:05 GMT

I'm merging 2 datasets

count = 6,437,567 variables = 22 file size = 1.2 GB page size = 65,536 # pages = 18,566
count = 2,276,587 variables = 917 file size = 2.4 GB page size = 131,072 # pages = 19,726

The resulting dataset has:

count = 6,437,567 variables = 924 file size = 21.8 GB page size = 131,072 # pages = 170,379

I didn't expect to get 22 GB when merging 1GB with 2GB

This is a straight merge without any calculations (dropping/merging a few fields)

I get the same results with a data step merge as with SQL (left join)

None of the character fields are excessively long (the longest is 40 and only a few are that long)

All 3 datasets use CHAR compression; no indexes yet

Dataset option REUSE = YES reduced the output from 23 GB to 22 GB

I'm using SAS-EG 9.0401M5 in Linux

Does it really make sense for the output dataset to be 22 GB ?

Re: Unexpected Size of Dataset

PeterClemmensen — Sun, 26 May 2019 19:24:17 GMT

Can you post your code please?

Re: Unexpected Size of Dataset

agoldma — Sun, 26 May 2019 19:44:29 GMT

This is the data step version:

data project.ds3 (reuse=YES) ;

   merge project.ds1 (in = inX )
         project.ds2  
         ;
   by field1
      field2
      ;
   if inX ;   * optional ;
run;

Compression is done automatically based on the setup of my SAS-EG

Re: Unexpected Size of Dataset

PeterClemmensen — Sun, 26 May 2019 20:27:57 GMT

Are you sure that the data set is compressed? What happens if you add the Compress=Char Option like this?

data project.ds3 (reuse=YES compress=char) ;

   merge project.ds1 (in = inX )
         project.ds2  
         ;
   by field1
      field2
      ;
   if inX ;   * optional ;
run;

Re: Unexpected Size of Dataset

Kurt_Bremser — Sun, 26 May 2019 21:17:09 GMT

Which compression rate is reported in the log?

Re: Unexpected Size of Dataset

agoldma — Sun, 26 May 2019 21:56:40 GMT

I know that the dataset ds3 is compressed because PROC CONTENTS says so and the log says that compression reduced the size by 54%

I still tried adding COMPRESS=CHAR (at the top) but it didn't change anything

I tried a few other things:

Data step copy -- produces the same size
PROC COPY -- produces the same size
PROC CPORT -- produces a smaller but still large transfer file of 14 GB
... PROC CIMPORT -- produces the same size (22 GB)
COMPRESS=BINARY -- reduced size to 10 GB (sill very large, coming from 1 + 2 GB)
PROC EXPORT to CSV = 9 GB (record length between 1045 and 19602)

Maybe 10 GB is the right answer (just hard to believe that 1 + 2 = 10)

Maybe this is why we made relational databases 50 years ago (and I should keep these 2 datasets separate)

Re: Unexpected Size of Dataset

Tom — Sun, 26 May 2019 22:12:19 GMT

10 sounds right. You are adding over 900 variables to your 6 million observations.

2.4*3 = 7.2 + 2.1 = 9.3

Re: Unexpected Size of Dataset

agoldma — Sun, 26 May 2019 22:21:34 GMT

When I imported that CSV file, I got 2 GB -- like I expected ... and it had bufsize=64k with CHAR compression

I tried making a dataset with bufsize=64k and BINARY compression... but it turned out to be 10GB

...even though BINARY compression reduced space by 79%

That CSV file must be missing something... or what it's missing is useless

Re: Unexpected Size of Dataset

FreelanceReinh — Sun, 26 May 2019 22:37:39 GMT

For each dataset we can roughly calculate the average amount of memory (bytes) occupied by each value:

(page size * # pages)/(# obs. * # variables). The results are about 8.6 for ds1, 1.2 for ds2 and 3.8 for ds3.

The fact that the average value in ds2 (probably including numeric values of 8 bytes and character values of varying lengths, possibly up to 40) occupies only 1.2 bytes suggests that the average observation of ds2 is highly compressed. It is quite possible, though, that the compression rate varies considerably between records: Observations with many missing character values will have much higher compression rates than observations with long non-missing values.

Observations of the latter type (in ds2) might have field1-field2 combinations whose BY groups in ds1 consist of several observations so that multiple copies of those long values are created. Thus, the distribution of compression rates can easily shift towards lower rates. (After all, it's plausible that you tend to match more non-missing values than missing values from ds2 to ds1.) But this means an increased average memory consumption in ds3 per value coming from ds2 (i.e. >1.2 bytes). The much larger space requirements per value from ds1 (8.6 bytes) increase the average in ds3 further -- why not to 3.8 bytes?

Re: Unexpected Size of Dataset

Reeza — Mon, 27 May 2019 00:17:18 GMT

You have variables overwriting each other as well. That doesn't concern you?

917+22 - 2 (linkage) = 937 but you only have 924.

Re: Unexpected Size of Dataset

agoldma — Mon, 27 May 2019 00:45:44 GMT

Hi Reeza,

Thank you for looking at this

I have a few variables overwriting eachother, but I'm not concerned about it because they have the same or nearly the same values.

When I imported the CSV file, I got 2 GB like I wanted... but... most of those 900 variables changed from numeric to character, so instead of 8 bytes they were using 1 byte... which explains the reduction from 10 GB to 2 GB.

I guess Tom and FreelanceReinhard are right -- that I shouldn't expect the same compression in the resulting dataset that I had in the source datasets.

Re: Unexpected Size of Dataset

Reeza — Mon, 27 May 2019 04:19:54 GMT

Did you import your data with PROC IMPORT? If it’s 1, I suspect you have a lot of missing, or truncated values.

Re: Unexpected Size of Dataset

agoldma — Mon, 27 May 2019 13:06:22 GMT

Yes, the import was bad or at least very different

One big difference was that most of the 900 variables changed from numeric to character of length 1

Length 1 is not sufficient for most of those variables

They have many missing values, so PROC IMPORT probably didn't work right

Expecting 2GB for the merged dataset is probably not realistic