Optimizing CAS Storage space

[stewart_Jardine]

Hi all,

we have been working with Viya 4 for quite a while now and have started to get to a point where we need to be a bit more clever with how we use storage in CAS... what we think is going to be a real benefit to us is the concept of DVR but we are struggling a bit with the syntax and wondered if someone out there might be able to help.

In the post linked above and another post about DVR it demonstrates the abilities of memoryFormat="DVR" using the table.copytable actionset and indeed if we create a copy of an existing CAS dataset using this option we can see a massive table size reduction 

as you can imagine this got us very excited and wanted to start using DVR on some of our bigger tables as soon as possible and this is where we started running in to syntax and potentially functional nightmares and tying ourselves in knots!

firstly unless i am missing something fairly obvious you cannot load a standard SAS table, from a standard library such as WORK into CAS memory using "PROC CAS"... far as we have been able to tell you HAVE to use PROC CASUTIL - Please do correct me if i am wrong on this!

This is where we enter the first loop of the knot... as far as we can tell you cannot set memoryformat using PROC CASUTIL ... so we have to load our table from work in to CAS using CASUTIL and the memoryformat is not set to DVR at this point.

at this point, we understood from the documentation that there was 2 ways to set the memoryformat option either used the table.copytable actionset or table.loadtable actionset.... we decided we would save the file down as a sashdat while we were still in CASUTIL and then use table.loadtable in a PROC CAS step:

proc casutil incaslib=CARDS outcaslib=CARDS;
	droptable casdata="InLifeEclScenario2" quiet;
	load data=Group_InLifeEclScen_Final casout="InLifeEclScenario2";
	save casdata="InLifeEclScenario2" replace;
	droptable casdata="InLifeEclScenario2" quiet;
quit;

proc cas;
	table.loadTable / 
		caslib="CARDS", 
		path="InLifeEclScenario.sashdat", 
		casout={name="InLifeEclScenario2", caslib="CARDS", promote=TRUE memoryFormat="DVR" replication=0};
quit;

this however results in a table exactly the same size as the original and the memory format doesn't seem to make a difference

however if we test using the table.copytable actionset :

proc cas; 
  table.copyTable /
    table={name="INLIFEECLSCENARIO" caslib="CARDS"}
    casOut={name="INLIFEECLSCENARIO_DVR" caslib="CARDS" memoryFormat="DVR" replace=True replication=0};
run;

we get the file size reduction we are looking for.

What I'm left with is thinking that I need to use CASUTIL to load data and then create a copy of that data and then save it and then delete the original... this cant be right and I must be missing something... any experts out there who can help point out the piece of the puzzle I am missing I would be very grateful.

[stewart_Jardine]

this is the best that I have been able to come up with so far (but it feels clunky and not optimised)

proc casutil incaslib=CARDS outcaslib=CARDS;
	load data=Group_InLifeEclScen_Final casout="InLifeEclScenario_temp";
quit;

proc cas; 
  table.copyTable /
    table={name="InLifeEclScenario_temp" caslib="CARDS"}
    casOut={name="INLIFEECLSCENARIO_DVR" caslib="CARDS" memoryFormat="DVR" replace=True replication=0};

	table.save / 
	table={name="INLIFEECLSCENARIO_DVR" caslib="CARDS"}, name="INLIFEECLSCENARIO_DVR.sashdat", caslib="CARDS",replace=TRUE;

	table.dropTable /           
	name="InLifeEclScenario_temp",	caslib="CARDS";
run;

[BrunoMueller · Accepted solution]

Use Proc CAS with the UPLOAD statement. It provides two advantages, you can specify to convert CHAR to VARCHAR and also specify the MEMORYFORMAT option.

proc cas;
  action table.droptable / name="somedata" quiet=true;
run;
  upload / path="%sysfunc(pathname(work))/somedata.sas7bdat"
    casout={
      caslib="casuser"
      name="somedata"
      promote=true
      memoryformat="DVR"
    }
    importoptions={
      filetype="BASESAS"
      varcharConversion=17
    }
  ;
run;
quit;

[stewart_Jardine]

this seems like a great option thanks