Invalid DataType Error for Proc SQL Query

I'm creating a basic query using Oracle Pass Through and a macro loop. It worked fine when I did Connect to Oracle, create table from connection but when I just try to use just the execute(DDL statements) by Oracle, it give me an invalid datatype error. I'm not sure how to fix it. I'm just creating tables from another existing table. I'm using SAS EG for this particular program but just the code editor not the query builder.

The &STATES macro resolves to a list of states in a global macro I created

I've written the same query successfully using the connect to Oracle, create table from connection pass through method but would like to also get it to work with just the execute method to see test which is more efficient. However, when I use the execute method, I get the datatype error.

%let NUM=2;      
%let FT=LT;    
%let STATES=&&STATESR&NUM;  

PROC SQL;
connect to oracle(path=&mydb user=&USER pw=&PW);

%macro DTCNT() / parmbuff; 
%let i=1;
%let ST=%scan(&SYSPBUFF,&I);  

%do %while (%str(&ST)^=);

execute(CREATE TABLE TMSIS_&ST._&FT._HDR_ADJDT_CNTS1 (bulkload=yes dbcreate_table_opts=nologging) AS
         SELECT adj_date,
                count(*) as row_cnt 
         from mcd_r&NUM._own.&ST._&FT._header_f
         group by adj_date
         order by adj_date) by oracle;

execute (commit) by oracle;

      %let i=%eval(&I+1);  
      %let ST=%scan(&SYSPBUFF,&I);
      %end;
%mend DTCNT;
%DTCNT(&STATES);

disconnect from oracle;
QUIT;

ERROR: ORACLE execute error: ORA-00902: invalid datatype.

Solution

You are using the EXECUTE() statement so the SQL inside that needs to be Oracle code. Oracle will not understand SAS style dataset options. You seem be asking to create a variable named bulkload with a data type of yes.

Perhaps you meant something like this?

execute(
  CREATE TABLE TMSIS_&ST._&FT._HDR_ADJDT_CNTS1 AS
  SELECT adj_date
       , count(*) as row_cnt
  from mcd_r&NUM._own.&ST._&FT._header_f
  group by adj_date
) by oracle;