ORA-00928: missing SELECT keyword, SQL statement was not passed to the DBMS, SAS will do the processing

Question

I am so frustrated with this piece of code. I'm trying to pass in values using syspbuff which I do all the time. However, I want to pass in multiple values but for this UNION code I'm trying to do, it's giving me trouble. I am going from Oracle to SAS which I assume is causing the problem but I'd like an answer as to why. Previously, I had the source tables in temp space (SAS) and I didn't get this error. But when I had to create the tables in MYDB (Oracle) because of a specific reason, I started getting the large log with "failure to pass through" errors.

Interestingly, the code actually works and it does what I want it to but the problem is that I get a pop up that the log is too large and will open externally. Then it opens a text file that is HUGE and has tons of errors basically saying that it couldn't pass through the code into implicit pass through. I wasn't trying to do pass through for this particular piece of code. So, again, it works and I ultimately get what I want but the log issue is driving me bonkers.

%macro ALLPROVTYPE() / parmbuff; 
    %do ii = 1 %to %sysfunc(countw(%bquote(&syspbuff.))); 
    %let FT=%scan(%bquote(&SYSPBUFF),&ii);  

CREATE TABLE MYSASLIB.ALLST_PROV_&FT._NULL AS
SELECT "AK" AS STATE,*
FROM MYDB.AK_PROV_&FT 

%macro JNSTS() / parmbuff; 
    %do i = 1 %to %sysfunc(countw(%bquote(&syspbuff.))); 
    %let ST=%scan(%bquote(&SYSPBUFF),&i); 

UNION CORR

SELECT "&ST" AS STATE,*
FROM MYDB.&ST._PROV_&FT

      %end;
%mend JNSTS;
%JNSTS(&&PROVALL&FT);
;
      %end;
%mend ALLPROVTYPE;

PROC SQL;
%ALLPROVTYPE(&PROVNUMS);
QUIT;

ACCESS ENGINE: ERROR: ORACLE prepare error: ORA-00928: missing SELECT keyword. SQL statement: DEBUG: DBMS engine returned an error - NO Implicit Passthru. DEBUG: Error during prepare of:

Answer 1

The way I understood this query is, you are creating multiple tables, and each table is created as a select statement which is constructed through multiple select statements that are joined via a UNION CORR. Essentially something like:

create table <something> as
  (select <something> as state, * from <something> union corr 
   select <something> as state, * from <something> union corr
   select <something> as state, * from <something>);

Is this correct?

If yes, your macro code had some syntactically problematic nesting going on. Try the following code (though I wasn't able to fully verify it since I don't have information about the inputs to the macros):

/* Since this needs to be passed between the two macros */
%global FT;

%macro ALLPROVTYPE() / parmbuff;
    %do ii = 1 %to %sysfunc(countw(%bquote(&syspbuff.)));
        %let FT=%scan(%bquote(&SYSPBUFF),&ii);
        CREATE TABLE MYSASLIB.ALLST_PROV_&FT._NULL AS (
            %JNSTS(&&PROVALL&FT)
            );
    %end;
%mend;

%macro JNSTS() / parmbuff;
    %do jj = 1 %to %sysfunc(countw(%bquote(&syspbuff.)));
        %let ST=%scan(%bquote(&SYSPBUFF),&jj);
        SELECT "&ST" AS STATE,* FROM MYDB.&ST._PROV_&FT

        %if &jj NE %sysfunc(countw(%bquote(&syspbuff.))) %then
        %do;
            UNION CORR
        %end;
    %end;
%mend;

PROC SQL;
    %ALLPROVTYPE(&PROVNUMS);
QUIT;