Hi,
I'm developing a DDC inserted into a VA Report, which basically contains a html form to let the user to "add/update/delete an order". An "order" is represented as a row on a global cas table "ORDERS". In the other parts of the Report, there are standard VA graphs which show some info about the ORDERS table (total orders per month, ...).
Now, when a user add/update/delete an order on my DCC, the information about this order is sent to a job which must update the global ORDERS table.
The problem is: how should the job update the ORDERS table to maintain consistency and handle concurrency?
What I have been implementing is very standard:
You can see at least two serious issues:
So the very problem here is that by design the global ORDERS table should never be dropped and replaced, it must always remains the same! But how to handle the classics insert/update/delete on a global table on CAS without dropping it?
data CASLIB.GLOBALTABLE(append=YES);
set CASUSER.LOCALTEMPTABLE;
run;
then the LOCALTEMPTABLE is inserted in the GLOBALTABLE without dropping it. Very well.
So actually I'm stuck, it seems I cannot use the CAS engine for my transactional application (i.e. with concurrent insert/update/delete on a table from multiple users with consistency and standard RDBMS concurrency rules).
What do you think? Am I missing something?
Thank you
Regards
As for the table.update you can use values returned back from a FEDSQL query, see example below.
One has to convert the type for numeric values and add quotes for char values to make it work
proc casutil;
load data=sashelp.class outcaslib="casuser" casout="class" replace;
load data=sashelp.class outcaslib="casuser" casout="class_update" replace;
run;
proc cas;
action fedsql.execdirect result=class_update status=sqlrc /
query="select name, age, height * 100 as newValue, trim( both from reverse(name) ) as newChar from casuser.class where age = 14"
;
describe class_update;
print sqlrc;
do row over class_update["Result Set"];
print row ;
/* convert value to string type */
newValue = catq("1", row["newValue"]);
/* add quotes for char value */
newchar = catq("a", row["newChar"]);
print "Updating: " row["name"];
action table.update status=updaterc /
table={
caslib="casuser" name="class_update"
where = catx(" ", " name = ", quote(trim(row["name"])) )
}
set={
{var="height", value=newValue }
{var="name", value=newChar }
}
;
print updaterc;
end;
/* action builtins.history;*/
run;
action table.fetch / table={caslib="casuser" name="class_update"};
run;
quit;
Nice trick Even though I expect huge performance issues when the rows to be updated are a lot, since the update is done one row at a time and it doesn't use parallelism at all!
What about delete? Is there maybe a similar trick?
I face the same issue for smaller chunks of data this works fine, but when you try to do updates on 100,000 records its super slow.
Hi,
I understand the needs for CRUD operations. I've written a blog series on that topic. They are not yet published externally but it will come ;-).
Basically, the technique I use is the same as yours: copy a temporary table and interact with that table. As you mention, the problem is concurrency. There is no real technique to avoid this as this would mean define a locking mechanism at row level which is kind of difficult to manage and handle on large scale environments.
In my blogs, I mention that even though it is possible to perform CRUD operations directly on the CAS table, it is better to use a database for that purpose. One reason being the rollback functionality. So, for a large scale environment, you should perform the updates using SAS Data Connectors to access an external database. You should then create a process to update the CAS table whenever needed (full reload or append).
To handle the multiple updates on the same record, you can use timestamp or compare the original values in your form with the one stored in the database.
With both techniques, you should decide what you want to do if the update is not performed.
I think there are many considerations when performing CRUD actions on data. Those considerations are not specific to SAS but CAS adds an extra layer of complexity as it not designed for CRUD activities.
Let me know if you have questions. I will do my best to answer based on the information you provide.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.