Easy sortable HTML tables in SAS ODS

Today Charlie Huang posted a SAS HTML table sorting demo, but without automation. Here is a fully automated implementation in SAS. The developer does not need to manually edit the HTML, and the user simply clicks any column and it sorts instantly client-side in the web browser. The feature is automatically applied to all SAS tables in the ODS HTML output.

I use a macro to avoid hard-coding URLs in all programs that sort tables. We keep ours macro in a macro library, so it’s easy to update to newer versions of jQuery and the tablesorter plugin.

/* The URLs are shortened because of */
/*   "Problem Note 37764: HEADTEXT values are limited to 256 characters" 
/*   <http://support.sas.com/kb/37/764.html> */
/* http://goo.gl/Pg0GB = https://ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js   */
/* http://goo.gl/ruKEb = http://cdn.jsdelivr.net/tablesorter/2.0.5b/jquery.tablesorter.min.js */

%macro ods_html_sort_table;
<script src='http://goo.gl/Pg0GB'></script>
<script src='http://goo.gl/ruKEb'></script>
<script>$(document).ready(function(){$('.table').tablesorter({widgets: ['zebra']});});</script>

ods html 
 /* Notice this style lets you add more HEADTEXT (maybe to the 256 characters) */

/* A simple table for demonstration */
proc print data=sashelp.class;

ods html text='Click on any column header to sort the table.';

/* PROC FREQ works */
proc freq data=sashelp.class;
	table sex * age/nopercent nocol norow;
/* PROC TABULATE does not sort well */
proc tabulate data=sashelp.class;
    class sex;
    var height;
    table sex, height*mean;


ods html close;

This was tested with SAS 9.3 TS Level 1M1 on Windows 7 64-bit with Firefox 18 and Internet Explorer 8.

A further improvement would be to find a CDN to host a tablesorter theme and integrate it, so the user sees the arrows making the sorting feature easy to discover.

After writing this, I searched the web and found others had accomplished something similar. For example in 2006 Covington and Oden published “Sorting ODS HTML Tables by Column Headers Using Javascript”, but the jQuery method is much more compact and elegant.

Incidentally, I was recently experimenting with a similar marriage of jQuery and SAS: I manually edited SAS HTML output to get the Accordion jQuery UI plugin working to create a sort of interactive table of contents for long SAS HTML reports, but I haven’t yet automated it.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s