Macros communicate SQLite and SAS without ODBC

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:         hchao8@gmail.com
*
****************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***************************************;