Make all SAS tables sortable in the output HTML

Most procedures in SAS generate stylish tables in the output HTML files. An option to sort the tables without bothering SAS again will be very handy for people who are familiar with Excel.

The JavaScript libary jQuery has a number of plug-ins. The tablesorter is one of them, which realizes the column-wise sorting. We only need to add several lines of JavaScript below into the very beginning of a SAS output HTML file. Then all tables in the HTML files are able to be sorted easily by clicking on the table head. For example, the resulting HTML from some SAS codes such as proc print data = sashelp.class; run; with the jQuery plug-in will have sortable effect above.

<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>

How to do it
First we open the HTML file by any text editor such as Windows Notepad, and the head lines of the file by the SAS command proc print data = sashelp.class; run;  will be like --

The second thing is to copy and paste the 7 lines of JavaSctipt above to the HTML file, just under the title tag. Then all is just done. All SAS tables can be sorted now as long as you have Internet connection. No server or other software is needed.

Another way to do it is to use a SAS macro. Andrew has a nice demo on his blog for implementing it.

SAS and D3.js (3): draw a map to display US cities' murder rates

Robert Allison has given an excellent map example by SAS/GRAPH about the murder rates per 100,000 people in the major US cities. To emulate this map, we should annotate the cities and use the size of the bubbles to represent the murder rates.

Comparing with the GRAPH module in SAS, the SVG based map on a webpage may have some advantages: first the maps will not lose details when zoomed out; second the JSON-formatted raw map data can be easily created to reflected the latest change; third with some more JavaScript attached the map on the HTML file can be empowered with dynamic effects.

1. Input raw data and transform SAS d to JSON 
I only choose the top 20 most violent cities, since I have to manually enter the coordinates data including longitude and latitude. Then I transform them to JSON format. To label all cities from the EXCEL file, a better way is to use R's ggmap package to automate the process of fetching the coordinates.

data top20;
input @1 City $11.    @14 State $2.    @17 Murder_rate @22 rank
@26 coordinates $50.;
cards;
New York     NY    471     1   -74.0059731,40.7143528
Chicago      IL    458     2   -87.6297982,41.8781136
Detroit      MI    363     3   -83.0457538,42.331427 
Los Angeles  CA    312     4   -118.2436849,34.0522342
Philadelphi  PA    302     5   -75.163789,39.952335 
Houston      TX    287     6   -95.3693896,29.7601927
Baltimore    MD    238     7   -76.6121893,39.2903848
New Orleans  LA    174     8   -90.0715323,29.9510658
Dallas       TX    166     9   -96.8004511,32.7801399
Washington   DC    144     10  -77.0363658,38.8951118
Saint Louis  MO    143     11  -90.296630859375, 38.74337300148123
Memphis      TN    132     12  -90.0489801, 35.1495343
Phoenix      AZ    122     13  -112.0740373, 33.4483771
Las Vegas    NV    111     14  -115.172816, 36.114646
Oakland      CA    104     15  -122.2711137, 37.8043637
Kansas City  MO    100     16  -94.5785667, 39.0997265
San Antonio  TX     99     17  -98.4936282, 29.4241219
Indianapolis IN     99     18  -86.1579557, 39.7685825
Jacksonville FL     99     19  -81.655651, 30.3321838
Cleveland    OH     83     20  -81.6954088, 41.4994954
;;;
run;

data top20_json(keep=string);
    set top20 nobs = nobs;
    length string $300.;
    _coordinates = cats('"coordinates":[', coordinates, ']},');
    _properties = cats('"properties":{"name":"', city, '","murder_rate":', Murder_rate, '}},');
    string = cats('{"type":"Feature","geometry":{"type":"Point",',_coordinates, _properties);
    if _n_ = nobs then substr(string, length(string), 1) = ' ';
run;

2. Draw the map and label the cities
D3.js provides the basic us-states.json data. The only thing that needs to do is to attache the cities' murder rate data to the HTML codes. A modern browser such as Chrome will compile the codes and generate the physical map.

SAS and D3.js (2): a macro to draw dynamic bar chart

More and more statistical softwares are able to create interactive web application. The recent shiny package by RStuido is a good example. Besides, in the world that JavaScript rules the browsers, D3.js seems to be very promising for rich data visualization.

One of the greatest things about D3.js is that it will bring interactivity to the web page by just a few JavaScript lines. For example, people like to see different angles of a bar chart to discover information. First the bar chart may be displayed from left to right alphabetically. Second it is sorted by each category's frequency. At the web era, a single dynamic bar chart would satisfy those requirements.

In SAS, we can wrap those effects into single macros for repeated usage.

1. Transform SAS dataset to JSON 
This time the SASHELP.CLASS dataset in SAS is still used. Since the objective is going to be a simple vertical bar chart, I only transform two variables WEIGHT and NAME into JSON format.

data class_JSON(keep=string);
   set sashelp.class nobs = nobs;
   length string $250.;
   _name = cats('{','"name":','"', name, '"', ',');
   if _n_ ne nobs then 
     _weight = cats('"weight":', weight,  '},');
   else _weight = cats('"weight":', weight, '}');
   string = cats(of _:);
run;

 2. Draw the bar chart
The x axis is corresponding to the kids' name from the SASHELP.CLASS dataset, while the y axis is for those kids' weight. The output file is name as vbar.html at the C drive. I add a check box on the top for user to turn on/off the sortable effect on the resulting SVG. Therefore The user will have the freedom to control the bar chart.
 

SAS and D3.js (1): a macro to draw scatter plot

HTML is the default output format for SAS since 9.3. Implementing the popular JavaScipt frameworks such as jQuery and D3.js will allow some dynamic effects to the final HTML results. In those cases the HTML file is static, and thus no HTTP server is needed.

At the beginning, I come up with a simple SAS macro to realize the scatter plot which is a SVG format picture on the canvas of a HTML page.

 1. Transform SAS dataset to JSON 
JSON is the dominant data format for web. Actually I found that SAS’s dataset can be easily transformed to JSON . Here the SASHELP.CLASS dataset is transformed to JSON by two consecutive DATA STEPs. Of course, a macro can be a beter way to simplify those steps, like Wade Buskirk's JSON.sas.

data class;
   set sashelp.class nobs = nobs;
   _weight = cats('{', '"weight":', weight,  ',');
   _height = cats('"height":', height,  ',');
   _age = cats('"age":', age,  ',');
   _name = cats('"name":','"', name, '"', ',');
   if _n_ ne nobs then 
      _sex = cats('"sex":','"', sex, '"', '},');
   else _sex = cats('"sex":','"', sex, '"', '}');
   keep _:;
run;

data class_JSON;
   set class;
   length string $250.;
   string = cats(of _all_);
   keep string;
run;

2. Draw the scatterplot
In the macro below, I set 7 parameters in the case to specify the incoming data, some plotting parameters such as x axis and y axis, and output path. The purpose of the macro is to wrap the HTML, CSS and JavaScript code and export the final HTML file. Besides the parameters in the SAS macro, every element like the color or style is modifiable in the JS script which is wrapped by the macro.

Finally the scatter plot is drawn on the generated HTML file. D3.js is the future of data visualization. Playing SAS and D3 brings me a lot of fun.