Compression Says NO

[AllanBowe]

In a default SAS environment, COMPRESS=NO.  You can set COMPRESS=YES|CHAR|ON (or BINARY) at the level of system option, library and dataset.

I have a table that's going to grow.  Here is what I tried:

options compress=char;
libname sascom (work) compress=char;
proc sql;
create table sascom.amgonnagrow(compress=char)(my num, your char);

Here's what happened:

NOTE: Compression was disabled for data set SASCOM.AMGONNAGROW because compression overhead would increase the size of the data set.

Compression was not applied!!

I tried the same thing with a data step:

data sascom.amgonnagrow(compress=char);
  length my 8 your $200;
  call missing(of _all_);
  stop;
run;

Success.

Is there any way to force an existing, empty table to have the compress applied?  Or some trick in SQL to apply the compress on initialisation?

[AllanBowe]

The issue with my example was, in fact, the short length of the observation.  As per the docs:  

The V9 engine compresses one observation at a time, and adds a fixed-length block of data to each observation. Because of the additional block of data, some data sets would result in a larger file size if compressed. For example, a data set with an extremely short observation length would not benefit from compression.

Indeed, increasing the length of the char var in SQL enables the compression:

73 proc sql;

74 create table demo16(compress=char)(my num, your char length=16);

NOTE: Compression was disabled for data set WORK.DEMO16 because compression overhead would increase the size of the data set.

NOTE: Table WORK.DEMO16 created, with 0 rows and 2 columns.

75 create table demo17(compress=char)(my num, your char length=17);

NOTE: Table WORK.DEMO17 created, with 0 rows and 2 columns.

There must be some other reason why my tables aren't compressed - am off now, to find it!

[Tom]

Why would a dataset "grow"?

Are you trying to use SAS as a transactional database system?

Just make a new dataset when it gets bigger.

[AllanBowe]

Hi Tom - we build HTML interfaces using SAS as a backend. Whilst we always recommend a database for transactional processing, in reality - as I'm sure you appreciate - it's not always possible. For sure we could ask the client to implement some batch job to rebuild the table on a regular basis, but that would be a last resort.

I actually found a way to apply the COMPRESS attribute to an empty table, and will share here once published!

[Tom]

You mean like this?

data test(compress=yes reuse=yes);
  length x1-x2 8;
run;
proc sql;
  delete * from test;
quit;

[AllanBowe]

actually the approach was to simply APPEND where the base table did not previously exist. This applied the compression, and also copies all of the constraints etc.
This macro serves: https://core.sasjs.io/mp__sortinplace_8sas.html

[yabwon]

Does it have to be SQL? A data step and proc append(replacing "insert") seems to be doing ok:

data A(compress=yes reuse=yes);
  stop;
  length i 8 x1 - x10 $ 2;
run;

proc contents data = A;
run;

data B;
  do i = 1 to 100;
    array x[10] $ 2 (10*"A");
    output;
  end;
run;

proc append base = A data = B;
run;

proc contents data = A;
run;

Bart