datastep.runcode loading from oracle

[acordes]

works:

LIBNAME myora ORACLE PATH=BIxxx.vwxxxx.de USER=SAS PASSWORD='xcxxxsds';

data MKT.CONSENTIMIENTOS(promote=yes);
set myora.CONSENTIMIENTOS;
run;

does not work:

proc cas;
function doesTableExist(casLib,casTable);
  table.tableExists result=r status=rc / caslib=casLib table=casTable;
  tableExists = dictionary(r, "exists");
  return tableExists;
end func;
tableExists = doesTableExist("mkt", "MKT.CONSENTIMIENTOS");
if tableExists=0 then 
dataStep.runCode result=r status=rc / code='/* BEGIN data step with the output table data */
data CONSENTIMIENTOS(caslib="mkt" promote=yes);
set CONSENTIMIENTOS(caslib="myora");
run;';
run;

proc cas;
dataStep.runCode result=r status=rc / code='/* BEGIN data step with the output table data */
data CONSENTIMIENTOS(caslib="mkt" promote=yes);
set MYORA.CONSENTIMIENTOS;
run;';
run;

error code

[SASJedi · Accepted solution]

Code running in CAS has absolutely no access to SAS libraries on the Compute Server. This means that your DATA step in CAS can't use data from the myOra libref in the SAS session. To use Oracle in CAS, you need an Oracle-based CASLIB. Here is some example code:

cas mySession;
/* Establish an Oracle caslib so you can access Oracle in CAS*/
caslib myOra desc="Oracle caslib" 
              datasource=(
                srctype="oracle"
               ,username="SAS"
               ,pwd='xcxxxsds'
               ,path="BIxxx.vwxxxx.de"
                ) 
      ;

proc cas;

function doesTableExist(casLib,casTable);
  table.tableExists result=r status=rc / caslib=casLib table=casTable;
  tableExists = dictionary(r, "exists");
  return tableExists;
end func;

/* 
 It's easier to write DATA steps in a source block than to write 
 the code within a string parameter. Use caslib.table_name format
 to reference CAS tables when running in CAS. 
*/
source myDataStep;
data mkt.CONSENTIMIENTOS(promote=yes);
   set myOra.CONSENTIMIENTOS;
run;
endsource;

tableExists = doesTableExist("casORA", "CONSENTIMIENTOS");
if tableExists=0 then do;
   /* Execute the DATA step code from the source block named myDataStep */
   dataStep.runCode result=r status=rc / 
	   code=myDataStep;
end;
run;

Remember that, in CAS, tables written to the Oracle caslib (myOra) are still CAS in-memory tables, and have NOT been saved to the Oracle database yet. You must explicitly save it to the database, for example using the table.save action.

May the SAS be with you!
Mark