Sortable tables in SAS

This is an update of my previous post Make all SAS tables sortable in the output HTML. 

Previously I manually added the sortable plugin to the SAS output. With the PREHTML statement of PROC TEMPLATE, the sortable HTML template now can be automately saved for the future use.

/* 0 -- Create the sortable HTML template */
proc template;
    define style sortable;
    parent=styles.htmlblue; 
    style body from body /
        prehtml='
            <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js"></script>
            <script src="http://cdn.jsdelivr.net/tablesorter/2.0.5b/jquery.tablesorter.min.js"></script>
            <script>
            $(document).ready(function( ) {    
            $(".table").tablesorter({widgets: ["zebra"]});
            });
            </script>
        ';
    end;
run;

/* 1 -- Make all the tables sortable */
ods html file = 'tmp.html' style = sortable;
proc reg data=sashelp.class;
    model weight = height age;
run;
proc print data=sashelp.class;
run;

While we explore the data or we like to change the order of SAS’s output tables, we only need to click the table heads, which is quite convenient.

Use MongoDB as a JSON factory

MongoDB is a persistent data store for JSON formatted data, which seems like an ideal middleware between the data tier software and the web. With MongoDB, Javascript's Map/Reduce functionality makes many trivial jobs particularly easy, such as translate an object to an array. For example, we can produce a bubble plot with Highcharts.js and the SASHEP.IRIS dataset in SAS very quickly.

Step 1: push SAS dataset toward MongoDB

First, let's push the SASHEP.IRIS dataset from SAS to MongoDB using thesas2mongo macro.

%sas2mongo(data = sashelp.iris, dbname = demo, collname = iris, tmpfolder = c:\tmp, mongofolder =c:\mongodb\bin);

Step 2: make the JSON file

Under the MongoDB shell, we could use Javascript'smap function to transform data to the desired structure.

var species = ["Setosa", "Versicolor", "Virginica"];
for (i=0; i<3; i++) {
   var z = db.iris.find({Species: species[i]}, {SepalLength:1,SepalWidth:1, PetalWidth:1, _id:0}).toArray().map(function(d){return [d.SepalLength, d.SepalWidth, d.PetalWidth]});
   print("{name:", JSON.stringify(species[i]), ", data:", JSON.stringify(z), "},");
};

Step 3: finalize Highcharts.js

The link of the final plots is here.

The plots are demonstrated into a Bootstrap 3 framework. One great advantages for SVG is that it is responsive to the devices‘ screens, which is especially friendly to mobile. The PNG or JPG formatted images can invoke Bootstrap’s responsive library to have the same effect.