manipulation of in memory table

[chriskoeo]

Hello,

I will give what I want to implement with an example below.
After this I will give you my two approches.
I am working on a 3.4 SAS Viya Platform.

EXAMPLE:
I have a table (MYTABLE) and this table is promoted on a global caslib (CKCAS).
This table contains 10 rows and 5 columns.

MYTABLE

column1	column2	column3	column4	date
aaa	4567	gtt	44	20210201
aa	5535	faas	44	20210202
fd	23	axv	44	20210203
sd	736	azxq	44	20210204
ghy	9008	feet	44	20210205
lk	3339	wqopp	44	20210206
yj	112	poo	44	20210207
trr	3634	piuy	44	20210208
hrfthr	689	iuyt	44	20210209
rt	2345	uio	44	20210210

The client asked from me to delete a few rows from the table.
His goal here is to retain the latest (by column 'date')  5 days.

Below is the "desired" table:

column1	column2	column3	column4	date
lk	3339	wqopp	44	20210206
yj	112	poo	44	20210207
trr	3634	piuy	44	20210208
hrfthr	689	iuyt	44	20210209
rt	2345	uio	44	20210210

IMPORTANT! The table needs to be promoted and accessible from all sessions!
Right now, there is a job every day that collects data for the client and append them on MYTABLE.
This implementation will not change!


APPROCHE 1

cas christos;

libname KAPPA cas caslib="CKCAS";

proc sql;

delete from KAPPA.MYTABLE

where date<20210206;

quit;
cas christos terminate;

If I execute the code below, will I achieve the goal table?
Will the table MYTABLE be promoted after the deletion of the rows?

End of APPROCHE 1



APPROCHE 2
The second approche that I thought is to save the table for memory to hard drive of the server.
Procced with the deletion there and then to promote the table again in memory.
With code:

cas christos;

caslib _all_ assign;

casdatalimit=ALL;
libname pathtohd '/tmp/tempo_directory/';

libname KAPPA cas caslib="CKCAS";

data pathtohd.MYTABLE;

set KAPPA.MYTABLE;

run;

proc sql;

delete from pathtohd.MYTABLE

where date<20210206;

quit;

proc casutil;
load data=pathtohd.MYTABLE casout="MYTABLE" outcaslib="CKCAS" replace promote;
run;

cas christos terminate;

end of APPROCHE 2



My clients' tables are containing hundredths of millions of rows and the most important is that the tables are on a Production Environment.

I think this implementation needs to be in memory.
Do you have any better idea?
Will any of my approches work?
**This implementation needs to be on over 100 tables + these tables updating from SAS jobs daily.
So, my second approche seems not the most appropriate.


Thnaks in advance,

[alexal]

@chriskoeo ,

Why don't you want to use deleteRows action?

[chriskoeo]

@alexal Can you please provide me an example with the "deleteRows" implementation?
With this approche table stays in memory and the "update" taking place in memory?
(Base your example on mine if you want)

[alexal]

@chriskoeo ,

Here is a basic example:

cas casauto;

proc casutil;
	load data=sashelp.cars
	replace;
quit;

proc casutil outcaslib="public";
	promote casdata="cars";
quit;

proc cas;
	table.deleteRows / table={caslib="public", name="cars", where="Make='Chrysler'"};
run;

cas casauto terminate;

Here is what you will see in the SAS log:

93 proc cas;

94 table.deleteRows / table={caslib="public", name="cars", where="Make='Chrysler'"};

95 run;

NOTE: Active Session now CASAUTO.

{rowsDeleted=15}

With this approche table stays in memory and the "update" taking place in memory?

That is correct.

[chriskoeo]

Hi,


Sorry for being late to reply.

Your code seems not working for me.

What I did..

proc casutil;
load data=sashelp.cars casout="cars" outcaslib="BI_EXP" promote;
run;

Table uploaded to memory successfully.

Then I tried to run:

proc cas;
table.deleteRows / table={caslib="BI_EXP", name="CARS", where="Invoice < 20000"};
run;

Below you can find the log output.

76 proc cas;

77 table.deleteRows / table={caslib="BI_EXP", name="CARS", where="Invoice < 20000"};

78 run;

NOTE: Active Session now CHRISK.

ERROR: The action 'deleteRows' could not be found in action set 'table'.

ERROR: The action stopped due to errors.

[alexal]

That code works on my server. What version are you using?

92   
93   proc cas;
94   table.deleteRows / table={caslib="public", name="cars", where="Invoice < 20000"};
95   run;
NOTE: Active Session now CASAUTO.
{rowsDeleted=122}

[chriskoeo]

Below you can find the content of "About" pop up

Product name:  SAS® Studio

Release:  5.1

SAS® Viya® release:  V.03.04

[alexal]

It looks like deleteRows action isn't available on SAS Viya version 3.4 - https://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=allprodsactions&docset.... I have version 3.5 installed on my server.

[chriskoeo]

So there is no way on my Platform / Version to implement this?

[alexal]

Correct, you won't be able to use deleteRows action on SAS Viya 3.4. Have you considered updating to version 3.5?

[chriskoeo]

This is not under my consideration.
It is on client's side.
At the moment, this is out of scope.

Is there any other way to delete rows from a table in memory?

[alexal]

Not sure, please reach out to SAS Technical Support.

[chriskoeo]

Thanks a lot @alexal  🙂