@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
