topic Re: How to create a temporary table in CAS in Developers

How to create a temporary table in CAS

AllanBowe — Sat, 09 May 2020 22:17:05 GMT

I'm running a Job Execution Service as the backend for a HTML5 web app using a shared service account on SAS Viya 3.5 as described here.

The challenge is, that my app is attempting to left join a WORK table with a large CASLIB table, which then fails with:

ERROR: The maximum allowed bytes (104857600) of data have been fetched from Cloud Analytic Services. Use the DATALIMIT option to increase the maximum value.

I'd rather not increase the limit, as the table could be very large. Instead I'd like to push my WORK table into CAS and perform the join there, returning the subset I need.

The challenge is, that my app will have many users, potentially running this join concurrently. My current plan is to create tables in the shared CASUSER library that are named using an md5() hash, thereby virtually guaranteeing uniqueness and avoiding a naming clash.

Whilst I'll certainly clean up this table afterwards, my concern is whether some unforeseen bug will prevent the cleanup operation. It would be better to create this table in a location that would be automatically cleared (like SASWORK).

Is there such concept of a CASWORK library? (implied here but that still ends with a proc delete)

Re: How to create a temporary table in CAS

MikeMcKiernan — Mon, 11 May 2020 14:20:57 GMT

Adding the data into CAS makes sense, as you describe. By default, the table that you transfer to CAS is session scoped. Only the session that transfers the data can access that session-scoped table. That should help sidestep the need for unique table names.

If you can use FedSQL, you can specify the SESSREF= option on the PROC FEDSQL statement to ensure that the join runs in CAS.

More info about session and global scope tables: Session and Global Scope

FedSQL: SESSREF= , and FedSQL programming for CAS

Re: How to create a temporary table in CAS

AllanBowe — Mon, 11 May 2020 16:21:33 GMT

This was exactly my approach. I put uniquely named tables in the CASUSER library and `proc fedsql` with the sessref option to join them.

Thanks!

Re: How to create a temporary table in CAS

joeFurbee — Tue, 12 May 2020 14:37:43 GMT

Hi @AllanBowe,

I see this has been answered, but wanted to add the response I had ready.

I spoke with a couple of SMEs on this. They agreed your plan is correct for your use case: create tables in the shared CASUSER library.

In this instance there is not a 1-to-1 equivalent way of handling in SAS9 vs SAS Viya. CASUSER is as close as your going to get to mimicking the SAS9 functionality. This is a situation where we have to think in terms of CAS programming vs directly translating SAS9 concepts into CAS.

I spoke with @SteveSober about the one-level naming. He mentioned the PROC DELETE step is not required, so long as the CASUSER table is not promoted.