topic Re: How to Perform Update in CAS tables ? in Developers

How to Perform Update in CAS tables ?

Rahul_B — Mon, 19 Aug 2019 11:29:45 GMT

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

Re: How to Perform Update in CAS tables ?

alexal — Mon, 19 Aug 2019 12:20:47 GMT

@Rahul_B ,

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

Re: How to Perform Update in CAS tables ?

Rahul_B — Mon, 19 Aug 2019 12:37:27 GMT

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.

Re: How to Perform Update in CAS tables ?

BrunoMueller — Tue, 20 Aug 2019 06:50:30 GMT

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/highlight/true#M359

Re: How to Perform Update in CAS tables ?

Rahul_B — Tue, 20 Aug 2019 13:09:55 GMT

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.

Re: How to Perform Update in CAS tables ?

Edoedoedo — Wed, 18 Sep 2019 18:52:59 GMT

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.

Re: How to Perform Update in CAS tables ?

mretter — Tue, 21 Sep 2021 11:37:58 GMT

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.

Re: How to Perform Update in CAS tables ?

mretter — Tue, 21 Sep 2021 12:04:49 GMT

Update is = adding the new columns