topic Viya 3.5 data in memory not available to all users in SAS Viya

Viya 3.5 data in memory not available to all users

jaleone1 — Fri, 05 Feb 2021 16:30:52 GMT

I have a process that appends new data to an in-memory table. When I run the process and then query the table, I see the total number of rows including the rows just appended. When teammates query the same table, they see only the pre-appended rows.

The table was already promoted when the rows were appended. If I put a proc casutil with promote after the append, I get an error saying the table is already promoted. It feels like I need something like and UNpromote/REpromote, but I can't find any reference that describes such a process.

Any help is greatly appreciated.

Re: Viya 3.5 data in memory not available to all users

utrocketeng — Fri, 05 Feb 2021 18:30:40 GMT

I did not find an effective solution to append tables directly in CAS. I create a table in SPRE, drop the table in CAS (if it exists), and then promote it back. we do this A LOT every hour. so I have a nice auto call macro that handles a wide range of activities around updating data in CAS, but i plucked out a few macros that

might help you out. i find them very useful.

you end up invoking the %checkcasresult() macro by providing source table and target table information.

good luck,

%MACRO UniformDelay();
	%PUT Insert Random Delay, Between 0 and 1 Second;

	DATA _null_;
		UniformDelay = rand('Uniform');
		rc=SLEEP(UniformDelay,1);
	RUN;
%MEND;

/* Macro for Dropping a Table from CAS */
%macro DropIt(name=);
proc casutil incaslib="public";
  droptable casdata="&name";   
/*   list files;                                      */
run; quit;
%mend;

/* Load table to CAS and Promt */
%macro LoadPromoteIt(name=);
proc casutil;              
   load data=work.&name outcaslib='public' promote ; 
   save casdata="&name" replace;
run; quit;
%mend;

/* Remove existing table from CAS if loaded already  */
%macro deleteCASdsifexists(lib,name);
    %if %sysfunc(exist(&lib..&name.)) %then %do;

		%put DeleteCASDSifExistsMacro;

		%DropIt(name=&name);

	%end;

	%if %sysfunc(exist(&lib..&name.)) %then %do;

		%put First Attempt Failed; Trying one more time;

		%UniformDelay();

		%DropIt(name=&name);

	%end;
%mend ;

%macro checkCASresult(Slib,Sname,Tlib,Tname,email);
	%PUT Source Data &Slib..&Sname.;
	%PUT Target Data &Tlib..&Tname ;
	%if %sysfunc(exist(&Slib..&Sname.)) %then %do;
		%put CheckResultMacro;

			%UniformDelay();

			%deleteCASdsifexists(&Tlib, &Tname);

			%UniformDelay();			
			%put loadTable;
			%LoadPromoteIt(name=&Sname);
	%end;	
	%else %do;
		%put table not there;
	%end;
%mend;

Re: Viya 3.5 data in memory not available to all users

BrunoMueller — Mon, 08 Feb 2021 14:00:14 GMT

To append data to an existing CAS table you can use a DATA Step together with the APPEND=YES data set option.

Please be aware that the table to append, has to have the exact same structure as the existing table, so variable names, types, sequence all have to match. The CAS table is not automatically persisted, so the data is only added to the in memory part.

Essentially it looks like:

data casuser.existing_data(append=yes);