BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AllanBowe
Barite | Level 11

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)

/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs
1 ACCEPTED SOLUTION

Accepted Solutions
MikeMcKiernan
SAS Employee

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 

View solution in original post

3 REPLIES 3
MikeMcKiernan
SAS Employee

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 

AllanBowe
Barite | Level 11
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!
/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs
joeFurbee
Community Manager

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.


Join us for SAS Community Trivia
SAS Bowl XLIII, The New SAS Developer Portal
Wednesday, August 14, 2024, at 10 a.m. ET | #SASBowl

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1751 views
  • 2 likes
  • 3 in conversation