How to Perform Update in CAS tables ?

[Rahul_B]

I have been Trying to perform updates on CAS tables.

I am able to perform simple updates in CAS, but not a complex ones.

proc cas;
cas_table.name ="CI_CUST_CONTACT_HISTORY_STAGE";
cas_table.where = "CONTACT_MONTH = 201907"; 

table.fetch /
table = cas_table;
run;

table.update /
table=cas_table
set={
{var="SEQUENCE_NO", value="SEQUENCE_NO*10"}
};
run;

table.fetch /
table = cas_table;
run;

How do i update  if i CONTACT_MONTH  more than 10000 contact_month  in where condition and variable to update SEQUENCE_NO is specific to the contact_month this is a complex update that am unable to do in CAS.

But what i tried to do use a loop that is time consuming.

%MACRO SCANLOOP;
/* First obtain the number of */
/* records in DATALOG */
DATA _NULL_;
IF 0 THEN SET hdfs_kim.CONTACT_UPD_JULY2019 NOBS=X;
CALL SYMPUT('RECCOUNT',X);
STOP;
RUN;

/* loop from one to number of */
/* records */
%DO I=1 %TO &RECCOUNT;
/* Advance to the Ith record */

proc cas;
cas_table.name ="CONTACT_UPD_JULY2019";
cas_table.where = "contact_key=442276500";
run;

table.update /
table=cas_table
set={
{var="SEQUENCE_NO", value="SEQUENCE_NO*100"}
};
run;

%END;
%MEND SCANLOOP;
/* Call SCANLOOP */
%SCANLOOP;
RUN;

Any help is much appreciated.

Thanks 

Rahul

[alexal]

@Rahul_B ,

Here is a good example of how to use table.fetch & table.update.

[Rahul_B]

i have used the same example used in my code, but i want update columns from one CAS table with another, that provision we dont have if in this example.

[BrunoMueller]

Have a look at the example I provided in this post, maybe it does what you need. I reads rows from one CAS table and update another CAS table

https://communities.sas.com/t5/Developers/Viya-CAS-tables-for-transactional-applications/m-p/555604/...

[Rahul_B]

Thanks @BrunoMueller - your trick worked for smaller dataset but did not worked for the large dataset had perfromance issue  and the memory untilisation is increased while running the process.

[Edoedoedo]

AFAIK, there is no way to massively update a CAS table "in place" (i.e. without dropping it and recreating it), as well as DELETE rows.

I understand CAS is for analytical, but I believe it lacks A LOT of database features.

I really hope that in the next releases the CAS will be more robust from this point of view, we had to rewrite tons of processes that worked correctly in LASR environment (which supports the UPDATE for instance) to implement workarounds for the things that cannot be made in CAS.

[mretter]

HEllo, @Edoedoedo, Have you found any way how to resolve the issue with updating of large data set on the metadata level without the lengthy reloading/recalculation all the data set?
Really important issue in use.
Thank you.

[mretter]

Update is = adding the new columns