@Quentin wrote: I would suggest sending this in to tech support, with a log showing both your libname statement and your attempt to create sqlite.shoes.  This looks bug-ish to me. From what you I can see from your logs, the DATA step is able to create the table, but it's not able to to insert records into it.  That's weird.  Just for fun, you might try a simpler test case, e.g.: data sqlite.foo; x=1; run; You might also show them in the log that PROC APPEND to create a new SQL table works (if it does), but the DATA step is not working. Is there a standard form for tech support? I have never done that before.   Just FYI, here is the simpler test case: OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX; %let APPLF = C:\Users\webb.sprague@cfc.wa.gov\applications.sqlite; libname sqlite odbc complete="dsn=SQLite3 Datasource; Driver={SQLite3 ODBC Driver}; Database=&APPLF.; OEMCP=1;"; proc datasets lib=sqlite nolist; delete blah; run;quit; data sqlite.blah; x=1; run; Here is the log output.  Note the weird "SELECT * FROM blah WHERE 0=1" ... :   36 %let APPLF = C:\Users\webb.sprague@cfc.wa.gov\applications.sqlite; ODBC: COMMIT performed on connection 0. ODBC: AUTOCOMMIT is NO for connection 0 37 libname sqlite odbc complete="dsn=SQLite3 Datasource; Driver={SQLite3 ODBC Driver}; 37 ! Database=&APPLF.; OEMCP=1;"; NOTE: Libref SQLITE was successfully assigned as follows: Engine: ODBC Physical Name: SQLite3 Datasource ODBC: AUTOCOMMIT is NO for connection 1 ODBC: AUTOCOMMIT turned ON for connection id 1 ODBC: Called SQLTables with schema of NULL 38 proc datasets lib=sqlite nolist; 39 delete blah; 40 run; NOTE: Deleting SQLITE.blah (memtype=DATA). ODBC_1: Prepared: on connection 1 SELECT * FROM blah WHERE 0=1 ODBC_2: Executed: on connection 1 DROP TABLE blah ODBC: 0 row(s) affected by INSERT/UPDATE/DELETE or other statement. NOTE: No data found/modified. 40 ! quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.03 seconds cpu time 0.01 seconds 41 data sqlite.blah; 42 x=1; 43 run; ODBC_3: Prepared: on connection 1 SELECT * FROM blah WHERE 0=1 ODBC: AUTOCOMMIT is NO for connection 2 ODBC_4: Executed: on connection 2 CREATE TABLE blah (x double) ODBC: 0 row(s) affected by INSERT/UPDATE/DELETE or other statement. ODBC: COMMIT performed on connection 2. ERROR: Error attempting to CREATE a DBMS table. NOTE: No data found/modified.. NOTE: The DATA step has been abnormally terminated. NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set SQLITE.blah may be incomplete. When this step was stopped there were 0 observations and 1 variables. ODBC: ROLLBACK performed on connection 2. ODBC: COMMIT performed on connection 2. ERROR: ROLLBACK issued due to errors for data set SQLITE.blah.DATA. NOTE: DATA statement used (Total process time): real time 0.04 seconds cpu time 0.03 seconds ODBC: Called SQLTables with schema of NULL ... View more

@Patrick wrote: The logs you share are pretty clear: You try to create a table that already exists. Not sure what makes you think it doesn't. Um, you are wrong : I delete the table, verify it doesn't exist via 2 methods (a refresh of the explorer window and Heidi SQL), then I run only the data step I mentioned in the original post.  You get a different error (posted) when you try to run the data step and the table already exists in the database.  Really.  I definitely appreciate your in depth response, but you should trust me when I say there is not table. ... View more

No, I am trying to get the "data sqlite.shoes; ..." statement to work as normal.  I have other work arounds where I create and drop the table using pass through SQL, then insert with proc sql.  Doing workarounds is what I am trying to avoid.   (I tried to quote but it didn't work...)   Thanks!     ... View more

@Patrick wrote: If this table already exists then the DB will throw an error. I assume that's what happens in your case. Well, you assumed wrong...  I confirmed that the table does NOT exist before running the code "data sqlite.shoes;  ..."  I looked via HeidiDB as well as a refresh of the explorer window.   Use below options to get more information in the SAS log what actually gets sent to the DB for execution. OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX; I will try that and report back.   [Edit]  For reference, when I run the datastep and the table exists, I get the following error in the log, compare to the original one I posted: ERROR: The ODBC table shoes has been opened for OUTPUT. This table already exists, or there is a name conflict with an existing object. This table will not be replaced. This engine does not support the REPLACE option. NOTE: The SAS System stopped processing this step because of errors. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds   [Edit #2] Here is the SQL output if I do the data step above (knowing that sqlite.shoes does NOT exist):   18 data sqlite.shoes; 19 set sashelp.shoes; 20 run; ODBC_4: Prepared: on connection 1 SELECT * FROM shoes WHERE 0=1 ODBC: AUTOCOMMIT is NO for connection 2 NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables. ODBC_5: Executed: on connection 2 CREATE TABLE shoes (Region varchar(25),Product varchar(14),Subsidiary varchar(12),Stores double,Sales double,Inventory double,Returns double) ODBC: 0 row(s) affected by INSERT/UPDATE/DELETE or other statement. ODBC: COMMIT performed on connection 2. ERROR: Error attempting to CREATE a DBMS table. NOTE: No data found/modified.. NOTE: The DATA step has been abnormally terminated. NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set SQLITE.shoes may be incomplete. When this step was stopped there were 0 observations and 7 variables. ODBC: ROLLBACK performed on connection 2. ODBC: COMMIT performed on connection 2. ERROR: ROLLBACK issued due to errors for data set SQLITE.shoes.DATA. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.01 seconds I think it's very interesting but somewhat surprising.  Also, I know by looking and inserting later that the table was definitely created on the SQLITE db.   Thanks! ... View more