CAS table is too big

[Muandre]

Dear all,

uploading und promoting my 800 MB SAS table leads to a 50 GB CAS table. What is my mistake?

cas;
caslib _ALL_ assign;

libname BIB1 oracle uid=*** user=*** ... ;

data work.test(compress=yes);
set BIB1.ds;
run;

proc casutil;
load data=work.test outcaslib="public"
casout="test" promote;
run;

[SASKiwi]

I suspect CAS tables are not compressed. What is the size of your SAS WORK table test when uncompressed.

[Muandre]

my sas work.test table is uncompressed 30 GB. What is the correct comress-way to load the compressed table to CAS?

[Kurt_Bremser]

CAS is built for speed and not for space, so compression is not wanted.

Get to know your data better; since you have a rather high compression ratio, this points to character variables that are defined longer than needed.

Inspect your charvars like

proc sql;
select max(length(charvar)) from have;
quit;

so you can do corrections of defined lengths before uploading to CAS; you can also check if there is a problem in your data import process that leads to overlong defined character variables (import from Excel tends to do that).

[SASKiwi]

According to the doc try this:

proc casutil;
load data=work.test (compress) outcaslib="public"
casout="test" promote;
run;

[ErikLund_Jensen · Accepted solution]

Hi @Muandre 

We ran into the same problem and found out that CAS supports the data type VARCHAR, so we dropped all experiments with compress.

We write the CAS table with a data step that defines new CAS variables with following statements:

• length newvar varchar(n);  *where n = longes string in input;
• newvar = oldvar;
• drop oldvar;

and so on..

In our setup the code is built into a DI Studio transformation that generates the data step from the actual input column specifications with proc execute. The incoming variables are renamed in the set statement, so the original names can be reused as new variable names.

[Muandre]

Thank you - our experiments with compress in proc casutil didn't help (well - they helped with size but as I learnt today: "CAS is built for speed and not for space, so compression is not wanted."

Working with length in our oracle-view helped <3. Thank you!

[SASKiwi]

@Muandre  - Your experience mirrors ours with the SAS VA LASR servers on SAS 9. You trim character variables to optimal length rather than compressing datasets.