topic Compression Says NO in SAS Programming

Compression Says NO

AllanBowe — Thu, 16 Dec 2021 22:46:39 GMT

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?

Re: Compression Says NO

AllanBowe — Thu, 16 Dec 2021 23:33:04 GMT

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

The V9 compresses one observation at a time, and adds a fixed-length of data to each observation. Because of the additional block of data, some 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!

Re: Compression Says NO

Tom — Thu, 16 Dec 2021 23:32:37 GMT

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.

Re: Compression Says NO

AllanBowe — Thu, 16 Dec 2021 23:37:15 GMT

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!

Re: Compression Says NO

Tom — Fri, 17 Dec 2021 01:53:50 GMT

You mean like this?

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

Re: Compression Says NO

yabwon — Fri, 17 Dec 2021 08:31:38 GMT

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

Re: Compression Says NO

AllanBowe — Sat, 18 Dec 2021 14:39:42 GMT

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