SQLite is an open-source relationship database management system with full functionality [Ref.1]. The light-weight (300k+ size) and zero configuration features distinguish it from its’ 800-pound counterparts like Oracle or MySQL. Thanks to the rise of mobile devices (plus SQLite-embedded Firefox), SQLite will probably be seen everywhere pretty soon.

I just love SQLite, since SQLite helped me learn not only writing SQL codes on Windows and Linux, but also managing complicated databases. Both Python and R have nice support for SQLite. And I always expect to implement SQLite as a frontend or backup for SAS. The shortcut is to apply some 3rd-party SQLite’s ODBC drivers [Ref. 2]. However, those drivers never worked very well on my workstations. To bypass the ODBC method, Wensui designed a macro to use SQLite’s ‘.dump’ operator to generate SQL file for SAS [Ref. 3]. To establish a two-way communication, I wrote two macros below to export SQLite’s table to SAS, and vice versa. The macros utilized tab-delimited text as medium, and SQLite’s batch mode to execute the script on a PC.

Reference:
1. Grant Allen, Mike Owens. ‘The Definitive Guide to SQLite’. Apress Publishing.
2. SQLite ODBC Driver. http://www.ch-werner.de/sqliteodbc/
3. Wensui Liu. ‘Sas Macro Importing Sqlite Data Table Without Odbc’.

/*******************READ ME*********************************************
* - Macros communicate SQLite and SAS without ODBC -
*
* SAS VERSION: 9.1.3
* SQLITE VERSION: 3.7.4
* DATE: 14may2011
* AUTHOR: [email protected]
*
****************END OF READ ME******************************************/

****************(1) MODULE-BUILDING STEP********************************;
******(1.1) BUILD A MACRO FROM SAS TO SQLITE****************************;
%macro sas2sqlite(sastable = , path = , database = );
/*****************************************************************
* MACRO: sas2sqlite()
* GOAL: output a dataset in SAS to a table in SQLite
* PARAMETERS: sastable = dataset in SAS for SQLite
* path = destinate file path for SQLite database
* database = name of SQLite database
*****************************************************************/
proc export data = &sastable outfile = "&path\sas_2_sqlite.txt" dbms = tab
repalce;
putnames = no;
run;

ods listing close;
ods output variables = _varlist;
proc contents data = &sastable;
run;
proc sort data = _varlist;
by num;
run;

data _tmp01;
set _varlist;
if lowcase(type) = 'num' then vartype = 'real';
else if lowcase(type) = 'char' then vartype = 'text';
run;
proc sql noprint;
select trim(variable) ||' '|| trim(vartype)
into: table_value separated by ', '
from _tmp01
;quit;

proc sql;
create table _tmp02 (string char(800));
insert into _tmp02
set string = '.stats on'
set string = 'create table sas_table(sas_table_value);'
set string = '.separator "\t"'
set string = ".import 'sas_path\sas_2_sqlite.txt' sas_table"
;quit;

data _tmp03;
set _tmp02;
string = tranwrd(string, "sas_table_value", "&table_value");
string = tranwrd(string, "sas_table", "&sastable");
string = tranwrd(string, "sas_path", "&path");
run;
data _null_;
set _tmp03;
file "&path\sas_2_sqlite.sql";
put string;
run;
options noxsync noxwait;
x "sqlite3 -init &path\sas_2_sqlite.sql &path\&database";

proc datasets;
delete _:;
quit;
ods listing;
%mend;

******(1.2) BUILD A MACRO FROM SQLITE TO SAS***************************;
%macro sqlite2sas(sqlitetable = , path = , database = );
/*****************************************************************
* MACRO: sqlite2sas()
* GOAL: output a table in SQLite to a dataset in SAS
* PARAMETERS: sqlitetable = table in SQLite for SAS
* path = target file path for SQLite database
* database = name of SQLite database
*****************************************************************/
proc sql;
create table _tmp0 (string char(800));
insert into _tmp0
set string = ".output 'output_path\sqlite_2_sas.txt' "
set string = '.separator "\t" '
set string = '.headers on'
set string = 'select * from sqlite_table;'
set string = '.output stdout'
;quit;
data _tmp1;
set _tmp0;
string = tranwrd(string, "sqlite_table", "&sqlitetable");
string = tranwrd(string, "output_path", "&path");
run;
data _null_;
set _tmp1;
file "&path\sas_2_sqlite.sql";
put string;
run;

options noxsync noxwait;
x "sqlite3 -init &path\sas_2_sqlite.sql &path\&database ";

proc import datafile = "&path\sqlite_2_sas.txt" out = &sqlitetable
dbms = dlm replace;
delimiter = '09'x;
guessingrows = 10000;
run;
proc datasets nolist;
delete _:;
run;
%mend;

****************(2) TESTING STEP****************************************;
******(2.1) TESTING THE FIRST MACRO*************************************;
data iris;
set sashelp.iris;
run;
%sas2sqlite(sastable = iris, path = c:\tmp, database = sas_sqlite.sqlite);

******(2.2) TESTING THE SECOND MACRO************************************;
proc datasets;
delete iris;
quit;
%sqlite2sas(sqlitetable = iris, path = c:\tmp, database = sas_sqlite.sqlite);

****************END OF ALL CODING***************************************;