SAS MACRO IMPORTING SQLITE DATA TABLE WITHOUT ODBC

shiyiming · 发表于 2010-10-22 13:24:01

From Wensui Liu's blog

********************************************************; * A SAS MACRO IMPORTING SQLITE DATA TABLE WITHOUT ODBC *; * ---------------------------------------------------- *; * REQUIREMENT: *; * HAVE SQLITE3.EXE DIRECTORY IN THE SYSTEM PATH. *; * LIMITATIONS: *; * 1. UNABLE TO HANDLE THE TABLE WITH BLOB DATA TYPE *; * 2. THE DEFAULT LENGTH OF ALL CHAR DATA IS 200 *; * 3. SYSTEM DEPENDENT (CHANGES NEEDED FOR OTHER OS) *; * 4. POTENTIALLY SLOW FOR LARGE TABLES *; ********************************************************; %let chlen = 200; options mlogic symbolgen mprint; %macro sqlite(path = , dbfile = , table = ); %let fileref = %trim(&path)\%trim(&dbfile); filename fileref "&fileref"; %if %sysfunc(fexist(fileref)) %then %do; %let dumpref = %trim(&path)\%trim(dump.txt); filename dumpref "&dumpref"; %if %sysfunc(fexist(dumpref)) %then %do; data _null_; rc = fdelete("dumpref"); run; %end; %let batref = %trim(&path)\%trim(dump.bat); filename batref "&batref"; %if %sysfunc(fexist(dumpref)) %then %do; data _null_; rc = fdelete("batref"); run; %end; data _null_; file batref; cmd = "sqlite3 &fileref "||'"'||".dump &table"||'"'||" > &dumpref"; put cmd; run; systask command "&batref" wait; %end; %else %goto exit; data create; infile dumpref; input; create = upcase(_infile_); if _n_ > 1 then output; if substr(create, 1, 2) = ');' then stop; run; proc sql noprint; select tranwrd(create, "TEXT", "CHAR(&chlen.)") into :create separated by ' ' from create; &create; quit; proc datasets library = work nolist; delete create(mt = data); run; quit; data _null_; infile dumpref end = eof; input; if _n_ = 1 then do; call execute("PROC SQL;"); end; if index(upcase(_infile_), 'INSERT INTO') > 0 then do; pos1 = index(_infile_, '('); pos2 = index(_infile_, ');'); insert = substr(_infile_, pos1, pos2 - pos1 + 2); call execute("INSERT INTO &TABLE VALUES"||insert); end; if eof then do; call execute("QUIT;"); end; run; data _null_; rc = fdelete("batref"); run; data _null_; rc = fdelete("dumpref"); run; proc contents data = &table varnum; run; %exit: %mend sqlite; %sqlite(path = D:\sas, dbfile = test.db, table = tblco);

		自动登录	找回密码
密码			立即注册