To fully leverage the distributed processing power of SAS Viya, you must explicitly direct PROC FEDSQL to the CAS server using the SESSREF= option. As a best practice, append the _METHOD option to your PROC FEDSQL statement; this prints the execution plan in the log, allowing you to instantly verify that your query actually executed in-memory and didn't quietly fall back to the local SAS Compute server.
The transition from SAS© 9.4 to SAS© Viya™ often comes with a learning curve regarding in-memory data management. A common confusion arises when transitioning from PROC SQL to PROC FEDSQL to interact with tables loaded in CAS libraries (CASLIBs).
If you have ever encountered the error "BASE driver, schema name... was not found", this article explains why it happens and how to fix your code.
Imagine you are working in a SAS© Viya™ environment. You have defined a path-based CASLIB and loaded a table into memory.
Here is the typical configuration:
| 1 | caslib mycaslib path="/sas/staging/test" datasource=(srctype="path") libref=mycas; |
| 2 |
Loading Data In-Memory:
You use PROC CASUTIL to load a table (e.g., test.sashdat) into the CAS server's memory.
| 1 | |
| 2 | PROC CASUTIL incaslib="mycaslib" outcaslib="mycaslib"; |
| 3 | |
| 4 | load casdata="test.sashdat" casout="test" replace; |
| 5 | |
| 6 | RUN; |
| 7 |
So far, everything is working. The table is accessible in memory under the name mycas.test.
To perform an aggregation (like a GROUP BY), you decide to use PROC FEDSQL, which is the standard SQL implementation for the CAS engine.
You attempt to run the following code:
| 1 | PROC FEDSQL; |
| 2 | CREATE TABLE mycas.counts AS |
| 3 | select FIELD, count(*) as N |
| 4 | from mycas.test |
| 5 | group BY FIELD; |
| 6 | QUIT; |
This is where the system returns the blocking error:
The error does not stem from a library configuration issue or a missing file. It is due to the way PROC FEDSQL communicates with the CAS server.
Unlike a classic SAS© procedure that runs locally, PROC FEDSQL needs to explicitly know in which CAS session it should operate to access in-memory tables. If no session is referenced, the procedure attempts to find the table via the local engine (BASE driver), which does not know the "MYCAS" schema defined on the remote server.
To fix this issue, you need to do two things:
Here is the corrected code:
| 1 | /* 1. Start or reference a CAS session */ |
| 2 | cas mySession sessopts=(caslib=mycas timeout=3600 locale="en_US" metrics=true); |
| 3 | |
| 4 | /* 2. Call PROC FEDSQL pointing to this session */ |
| 5 | PROC FEDSQL sessref=mySession; |
| 6 | CREATE TABLE mycas.counts AS |
| 7 | select FIELD, count(*) as N |
| 8 | from mycas.test |
| 9 | group BY FIELD; |
| 10 | QUIT; |
The codes and examples provided on WeAreCAS.eu are for educational purposes. It is imperative not to blindly copy-paste them into your production environments. The best approach is to understand the logic before applying it. We strongly recommend testing these scripts in a test environment (Sandbox/Dev). WeAreCAS accepts no responsibility for any impact or data loss on your systems.