Compress library tips based on an existing macro

[#1]

I found the macro, Sample 48809: Determine whether SAS data sets in a library are compressed, and if they are not, auto..., for compressing a library. I added a couple of improvements:

1. Pick only DATA memtypes (drop view, for example).

2. Pick only uncompressed members.

3. Only continue if at least one record was found.\

I've thought of another improvement, which would be to compress the dataset and make sure the compressed dataset is smaller than the uncompressed datset (sometimes they aren't) and then don't save the compressed version if it's larger.

/* compress all uncompress datasets within a library */

%macro compress_library(libname);

    %let libname = %upcase(&libname);

    /* Use the DICTIONARY.TABLES to obtain the name of the SAS data set (memname) and whether the SAS 
    data set is compressed (COMPRESS) and store in a SAS data set, INFOSQL */
    proc sql;
        create table infosql as
            select memname, compress
                from dictionary.tables
                    where libname = "&libname" and compress='NO' and memtype='DATA';
    quit;

    %if &SYSNOBS > 0 %then %do;

        /* Use DATA _NULL_ step to create 2 macro variables - one is the name of the SAS data set that is 
        not compressed and the other the number of SAS data sets not compressed */
        data _null_;
            set infosql end=last;


            i+1;
            call symputx('n'||trim(left(put(i,8.))),memname);

            if last then
                call symputx('count',i);
        run;

        /* Run the macro DO loop from 1 to &count to compress each SAS data set not compressed in the SAS 
        data library */
        %do i=1 %to &count;

            data &libname..&&n&i(compress=yes);
                set &libname..&&n&i;
            run;
        
        %end;        

    %end;

%mend compress_library;

[AndrewHowell]

Tom - here's a simplified version of the code, which uses an "open range" PROC SQL INTO :mvar statement to negate having to create and read the interim data set. (Unless you WANT that data set, that is..)

Good challenge to test if compression is worthwhile - I'll have to think on that.

/* compress all uncompress datasets within a library */
%macro compress_library(libname);
    %let libname = %upcase(&libname);
    proc sql noprint;
		select memname into :T1- from dictionary.tables where libname = "&libname" and compress='NO' and memtype='DATA';
		%let TCount=&SQLOBS;
    quit;
    %if &TCount eq 0 %then %put There are no uncompressed data sets to compress!;
	%else %do;
        /* Run a macro DO loop to compress each uncompressed SAS data */
        %do i=1 %to &TCount;
			%put Compressing: &libname..&&T&i;
            data &libname..&&T&i(compress=yes);
                set &libname..&&T&i;
            run;
        %end;        
    %end;
%mend compress_library;

[tomrvincent]

I like it!  Thanks!

I'm going to add another filter to it (filesize>200000) since I've noticed that datasets smaller than that usually don't compress well (either at all or get bigger).

[tomrvincent]

Here's my new version.  This one picks filesize>200000 and compresses each dataset to work first to see if the size decreases due to compression.

/* compress all uncompress datasets within a library */
%macro compress_library(libname);
       
    %let libname = %upcase(&libname);
    
    proc sql noprint;
        select memname into :T1- from dictionary.tables
        where libname = "&libname" and compress='NO' and memtype='DATA' and filesize>200000;
        %let TCount=&SQLOBS;
    quit;
    
    %if &TCount eq 0 %then %put There are no uncompressed data sets to compress!;
    
    %else %do;
        /* Run a macro DO loop to compress each uncompressed SAS data */
        %do i=1 %to &TCount;
            proc sql;
                select filesize format=decimal20. into :sizeb4 from dictionary.tables
                where libname = "&libname" and memname="&&T&i" and compress='NO' and memtype='DATA' and filesize>200000;
            quit;
            /* data &libname..&&T&i(compress=yes);*/
            data work.&&T&i(compress=yes); set &libname..&&T&i; run;
            proc sql;
                select filesize format=decimal20. into :sizeaft from dictionary.tables
                where libname = "WORK" and memname="&&T&i" and memtype='DATA';
            quit;
            %if &sizeb4 > &sizeaft %then %do;
                %put Compressing: &libname..&&T&i;
                data &libname..&&T&i(compress=yes); set work.&&T&i; run;
            %end;
            proc delete data=work.&&T&i; run;
        %end;        
    %end;
        
%mend compress_library;