topic Re: CAS table is too big in SAS Programming

CAS table is too big

Muandre — Wed, 14 Apr 2021 04:31:43 GMT

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;

Re: CAS table is too big

SASKiwi — Wed, 14 Apr 2021 04:43:51 GMT

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

Re: CAS table is too big

Muandre — Wed, 14 Apr 2021 05:09:04 GMT

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

Re: CAS table is too big

Kurt_Bremser — Wed, 14 Apr 2021 06:55:24 GMT

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).

Re: CAS table is too big

SASKiwi — Wed, 14 Apr 2021 07:04:45 GMT

According to the doc try this:

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

Re: CAS table is too big

ErikLund_Jensen — Wed, 14 Apr 2021 12:56:11 GMT

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.

Re: CAS table is too big

Muandre — Wed, 14 Apr 2021 13:48:47 GMT

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!

Re: CAS table is too big

SASKiwi — Wed, 14 Apr 2021 19:55:16 GMT

@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.