topic Re: proc casutil vs proc fedsql in SAS Programming

proc casutil vs proc fedsql

smm662002 — Wed, 28 Sep 2022 15:27:13 GMT

Hello,

SAS VIYA 3.5 patched until March 2022

Would anyone know why I have an error message (please see below) when I try to load a table from an Oracle caslib to CAS using proc casutil but is working when I am using proc fed sql?

ERROR: Base table or view not found ORA-00942: table or view does not exist

ERROR: Function failed.

ERROR: The action stopped due to errors.

I mention that the table is available in the caslib and proc fed sql took about 20min to run without any errors.

Thank you.

smm662002

Re: proc casutil vs proc fedsql

a20121248 — Sat, 01 Oct 2022 06:29:53 GMT

Hi there:

We have Viya 3.5 and we read tables from Oracle in 2 steps:

Create a caslib to Oracle
SAS Data Connector to Oracle
We tested 2 ways:
1. Use a proc cas;
  Query CAS Tables Using the FedSQL.execDirect Action
2. Use a proc fedsql sseref=casauto;

About the time (20min)... check the query execution plan (enable iptrace) and the logs (enable message level) to make sure the data is not going to the controller but to the worker nodes directly. If possible, use an explicit pass-through and compare the time (assuming no cross-datasources join).

Best,

Javier