topic Re: Thoughts on using SAS compression in SAS Data Management

Thoughts on using SAS compression

supp — Fri, 27 Dec 2019 15:07:24 GMT

Hello SAS community. In my SAS shop we have many users and can work with large datasets. We use SAS 9.4 on a Linux OS. Every so often we are reminded to delete datasets no longer needed or to consider compressing datasets using SAS compression.

As I am sure many of you already know SAS currently offers two compression algorithms, character (using the RLE algorithm ) or binary (using the RDC algorithm). My understanding is character compression generally works better when the data set is mostly character data. The binary compression generally works better when the data set has mostly numeric data.

I would love to hear from the community how you determine if SAS compression should be used? Is there an official policy? Or does each user decide for themselves if they want to compress their data sets? If you do use SAS compression how are you deciding which option (char or binary) to use? Are there any good "rules of thumb" that can be applied?

I understand the disadvantages of SAS compression can be increased CPU usage and actually increasing the size of the data set. Is there any other disadvantages you have come across using SAS compression? Any other considerations that should be taken into account before compressing data sets?

Re: Thoughts on using SAS compression

tomrvincent — Fri, 27 Dec 2019 15:55:07 GMT

I've found that normalizing and compressing the datasets saves more spaces than compression alone. Getting rid of redundant data really pays off.

Re: Thoughts on using SAS compression

supp — Fri, 27 Dec 2019 16:01:42 GMT

Thanks @tomrvincent , that makes a lot of sense. Are you using one of the SAS compression options when you compress your datasets? If so, how do you decide which one to use?

Re: Thoughts on using SAS compression

TomKari — Fri, 27 Dec 2019 16:14:32 GMT

Hi, supp

After never having used compression in 30 years, I recently bumped across a use case where it worked perfectly. This is a text analysis project, with a huge number of text strings, of which the length can vary wildly (most short, but a few very long).

I can't remember the exact number, but using the RLE algorithm reduced the size of the datasets by about 90%. I didn't notice any difference in processing time.

Tom

Re: Thoughts on using SAS compression

supp — Fri, 27 Dec 2019 16:19:15 GMT

@TomKari , that is a really good result! Being your data is mostly (or all) character data I am guessing RLE give you the best result. Out of curiosity did you also try binary compression?

Re: Thoughts on using SAS compression

tomrvincent — Fri, 27 Dec 2019 16:35:40 GMT

What I do is compress each resulting normalized dataset (dimension/fact table, if you will) if doing so actually saves space (sometimes it doesn't). I haven't bothered to try the different options just because I've already saved thru normalization. The rest is gravy. 🙂

Re: Thoughts on using SAS compression

mkeintz — Fri, 27 Dec 2019 16:52:22 GMT

I agree that you are describing a good use case. The downside of sas compressed datasets is that you are restricted to sequential data processing. I.e. no indexes, no SET ... POINT=. However normalization without compression might save substantial space and still support direct access.

Re: Thoughts on using SAS compression

TomKari — Fri, 27 Dec 2019 16:54:07 GMT

No, given that the expected compression benefits would come from the long character fields, I only used the RLE algorithm.

Re: Thoughts on using SAS compression

Kurt_Bremser — Fri, 27 Dec 2019 17:47:06 GMT

Almost all our production datasets are compressed with character (RLE). When writing a new batch job, I use compress=yes and look what the log tells me. If I get less than 10% compression rate, I omit compression.

Not only does compression save disk space, it also speeds up the ETL processes which are (almost) always I/O bound and benefit from having to move less data from and to storage. Care must be taken when processing datasets with a high compression rate. Sorting can overload the SASUTIL location, so using tagsort may be necessary. And don't forget to also use compression in WORK when dealing with such datasets.

Re: Thoughts on using SAS compression

supp — Fri, 27 Dec 2019 18:01:09 GMT

Great points @Kurt_Bremser ! Is there ever a scenario you would use binary compression instead of char? Or why do you prefer to use char compression?

Re: Thoughts on using SAS compression

SASKiwi — Fri, 27 Dec 2019 21:55:38 GMT

Where I work we decided to set compression to BINARY as a default option when starting all SAS sessions via SAS AUTOEXEC programs. We have been doing this for both SAS 9.3 and 9.4 over many years. This has worked well for us by conserving disk space and also ensuring we almost never run out of WORK space unless someone does a really silly query.

We consume a lot of data from SQL Server and the BINARY setting means we don't need to worry about shortening long character variables as they are all compressed. Uncompressed tables can be often around 5 times larger than uncompressed ones! For the type of datasets we have BINARY provides better compression than CHARACTER.

I also know of other SAS sites that compress their data by default.

Re: Thoughts on using SAS compression

supp — Fri, 27 Dec 2019 22:18:55 GMT

Thanks @SASKiwi . You stated that for your type of datasets binary is the better option. Is this because you have a lot of numeric data in your datasets? Or how did you determine binary was the better option?

As @mkeintz mentioned a compressed dataset can't take advantage of an index, do you ever find this to be problematic?

Re: Thoughts on using SAS compression

SASKiwi — Fri, 27 Dec 2019 22:58:53 GMT

@supp - I did some testing on some of our typical datasets and found BINARY gave better compression of around 10 percent. I also found that SAS jobs processing large datasets ran faster because of the reduced IO. While elapsed time was less CPU time increased, but only by a few percent. IMO universal compression is definitely worth considering if you process a lot of medium to large datasets.

Re: Thoughts on using SAS compression

Patrick — Sat, 28 Dec 2019 02:20:54 GMT

@mkeintz wrote:
I agree that you are describing a good use case. The downside of sas compressed datasets is that you are restricted to sequential data processing. I.e. no indexes, no SET ... POINT=. However normalization without compression might save substantial space and still support direct access.

@mkeintz Are you sure? That's not what the SAS log tells me.

28         options ps=max msglevel=i;
29         data have(compress=yes index=(indvar));
30           length charvar1000 $1000;
31           call missing(charvar1000);
32           do i=1 to 100000;
33             if mod(i,10)=1 then indvar+1;
34             output;
35           end;
36           stop;
37         run;

NOTE: The data set WORK.HAVE has 100000 observations and 3 variables.
INFO: Multiple concurrent threads will be used to create the index.
NOTE: Simple index indvar has been defined.
NOTE: Compressing data set WORK.HAVE decreased size by 96.23 percent. 
      Compressed is 59 pages; un-compressed would require 1565 pages.
NOTE: DATA statement used (Total process time):
      real time           0.10 seconds
      cpu time            0.12 seconds
      

38         
39         data want1;
40           do point=1 to nobs by 1000;
41             set have point=point nobs=nobs;
42             output;
43           end;
44           stop;
45         run;

NOTE: The data set WORK.WANT1 has 100 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

46         
47         proc sql;
48           create table want2 as
49           select *
50           from have
51           where indvar=10
52           ;
INFO: Index indvar selected for WHERE clause optimization.
NOTE: Table WORK.WANT2 created, with 10 rows and 3 columns.

53         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

Re: Thoughts on using SAS compression

supp — Sat, 28 Dec 2019 02:33:20 GMT

@Patrik, thanks for sharing your findings! That is very interesting. Do you mind sharing what version of SAS and Operating System were used for your tests?

Re: Thoughts on using SAS compression

Patrick — Sat, 28 Dec 2019 02:37:08 GMT

AUTOMATIC SYSVLONG 9.04.01M5P091317
AUTOMATIC SYSHOSTINFOLONG Linux LIN X64 3.10.0-862.14.4.el7.x86_64 #1 SMP Wed Sep 26 15:12:11 UTC 2018 x86_64 CentOS Linux release 7.5.1804 (Core)

Re: Thoughts on using SAS compression

Astounding — Sat, 28 Dec 2019 03:07:21 GMT

Before considering compression, think about whether you have good rules in place for the lengths used within the SAS data set. Many times, I have seen SAS data sets use $200 characters for a field that only needs a few characters ... usually because the database definition for the field was varchar200. Those who set up the field in the data base took advantage of the fact that varchar automatically adjusts to the number of characters needed. But when extracted, SAS uses the full length of 200 every time. If you have processes to examine fields and the length that they actually require, you will be a step ahead of the game whether or not you add compression afterwards.

Re: Thoughts on using SAS compression

mkeintz — Sat, 28 Dec 2019 04:20:15 GMT

Well, I'm definitely not sure any more. My understanding was that some compressions generated irregular observation lengths, making the implementation of direct access exceedingly tricky, and not implemented by SAS.

The SAS index records the index value and each RID (record id) associated with the index value. When every record is the same size, as would be the case with uncompressed SAS data sets, knowing the RID lets you know exactly which physical page (which are also of constant size) of data contains the record(s) of interests, which in turn means you can directly access only the pages needed. I'm not clear on how knowing the RID for compressed data sets can let you know which pages to read, … unless the compression keeps the observations uniform in length.

Thanks for the example. I'll have to re-map my understanding of SAS compression.

Re: Thoughts on using SAS compression

Kurt_Bremser — Sat, 28 Dec 2019 08:20:46 GMT

You "only" have to switch from observation number to observation start position to address the observation from an index. Given 64-bit processing, this is not so hard to do.

PS it might be that binary compression puts observation boundaries within bytes, and then this would not work anymore. Maybe @Patrick could rerun his experiment with compression=binary to clear this up?

Re: Thoughts on using SAS compression

SASKiwi — Sat, 28 Dec 2019 20:21:41 GMT

@supp - Regarding limitations with compression and indexes. As stated elsewhere, we universally compress with the BINARY option and never had any problems with indexes. I'm pretty sure we don't use direct access (POINT=), but if we did you can just add COMPRESS = NO to the DATA steps using it to avoid any problems.

Please note that universal compression means WORK libraries as well so the benefits of compressing apply to controlling WORK space as well.