Thursday, August 29, 2013

A SAS macro that exports data to MongoDB

MongoDB is possibly the most popular NoSQL data store. To bypass schema and constraint, I feel quite convenient to implement MongoDB as buffer to accompany current RDBMS .Also it is straightforward to use MongoDB and other tools (MEAN) to build some simple web apps for statistics presentation.
Neither SAS nor 10gen so far published any SAS-MongoDB driver. However, the table-like dataset in SAS can be transformed to CSV by PROC EXPORT or DATA Step. MongoDB has a nice API mongoimport that easily accepts CSV formatted data. I write a macro in SAS below to transport data from SAS to MongoDB. The speed is quite fast.
****************(1) MODULE-BUILDING STEP********************************;
%macro sas2mongo(data =, dbname = , collname =, tmpfolder =, mongofolder = );
   /*************************************************************************
   *  MACRO:      sas2mongo()
   *  GOAL:       output a dataset in SAS to a collection in MongoDB
   *  PARAMETERS: data       = SAS dataset to export
   *              dbname     = database name in MongoDB
   *              collname   = collection name in MongoDB
   *              tmpfolder  = Windows directory for temporary file exchange
   *              mongofolder= bin directory where MongoDB was installed
   *************************************************************************/
    proc export data=&data outfile="&tmpfolder.\tmp.csv" dbms=csv replace;
    run;
    options noxsync noxwait;
    %put the execuated command is: &mongofolder\mongoimport.exe -d 
        &dbname -c &collname --type csv --file &tmpfolder.\tmp.csv --headerline;
    x "&mongofolder\mongoimport.exe -d &dbname -c &collname --type 
        csv --file &tmpfolder.\tmp.csv --headerline";
%mend;

****************(2) TESTING STEP****************************************;
%sas2mongo(data = sashelp.class, dbname = demo, collname = class,
    tmpfolder = c:\tmp, mongofolder =c:\mongodb\bin);
Then I run commands in Mongo shell. It works just well.
use demo;
db.class.find();

Good math, bad engineering

As a formal statistician and a current engineer, I feel that a successful engineering project may require both the mathematician’s abilit...