BookmarkSubscribeRSS Feed
chriskoeo
Fluorite | Level 6
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
column1column2column3column4date
aaa4567gtt4420210201
aa5535faas4420210202
fd23axv4420210203
sd736azxq4420210204
ghy9008feet4420210205
lk3339wqopp4420210206
yj112poo4420210207
trr3634piuy4420210208
hrfthr689iuyt4420210209
rt2345uio4420210210
 
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:


column1column2column3column4date
lk3339wqopp4420210206
yj112poo4420210207
trr3634piuy4420210208
hrfthr689iuyt4420210209
rt2345uio4420210210

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,

 
12 REPLIES 12
alexal
SAS Employee

@chriskoeo ,

Why don't you want to use deleteRows action?

chriskoeo
Fluorite | Level 6
@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
SAS Employee

@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
Fluorite | Level 6

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
SAS Employee

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
Fluorite | Level 6
Below you can find the content of "About" pop up

Product name:  SAS® Studio
Release:  5.1
SAS® Viya® release:  V.03.04
 
 
 
alexal
SAS Employee

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
Fluorite | Level 6

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

alexal
SAS Employee
Correct, you won't be able to use deleteRows action on SAS Viya 3.4. Have you considered updating to version 3.5?
chriskoeo
Fluorite | Level 6

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
SAS Employee

Not sure, please reach out to SAS Technical Support.

chriskoeo
Fluorite | Level 6

Thanks a lot @alexal  🙂

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Discussion stats
  • 12 replies
  • 1415 views
  • 3 likes
  • 2 in conversation