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.

Use D3.js to draw pie chart instead of PROC GCHART

Is the pie really round?

Commonly the PIE statement in the PROC GCHART produces the pie charts. Given the shortcoming of the pixel based Gxxxxxx procedures, the boundary of the pie is not very clear. I always feel that the resulting pie is not 100% round. Let's see one example with a dataset from SAS's online document.

/* Set the graphics environment */
goptions reset=all;  

/* Create the input data set SALES */
data sales;
   input site $ 1-8 count;
   datalines;
Atlanta  103
Chicago  486
Dallas   195
Denver   400
New York 307
Seattle  577
;
run;

/* Produce the chart */
proc gchart data=sales;
   pie site / sumvar=count slice = inside value = none noheading;        
run;

My intuition is that the pie above has bumps on the boundary areas. In SAS, the SVG-based SG procedures can be a rescue. However, right now they don't include an option of the PIE chart (because a pie chart is not a statistical graph?)

An alternative solution

D3.js, an already very popular open-source JavaScript library, will soon launch its 3rd version. With the JavaScript interpreter of any browser such as Chrome, IE9 or Firefox, we can make a variety of data-driven graphs with D3. The good thing is that D3 generates SVG, which can be zoomed to any size without losing detail.

In this example, I switch to a plug-in of D3 to draw the pie again. This time I finally obtain a really round pie.

Sorting in Python

#-------------------------------------------------------------------------------
# Name:        Methods of sorting
# Purpose:     implements the sortings mentioned by Robert Sedgewick and
#               Kevin Wayne, Algorithms 4ed
#
#-------------------------------------------------------------------------------

def selection_sort(a):
    for i in range(len(a)):
        min = i
        for j in range(i+1, len(a)):
            if a[j] < a[min]:
                min = j
        a[i], a[min] = a[min], a[i]

def insertion_sort(a):
    for i in range(len(a)):
        j = i
        while j > 0:
            if a[j] < a[j-1]:
                a[j], a[j-1] = a[j-1], a[j]
            j -= 1

def shell_sort(a):
    h = 1
    while h <= len(a)/3:
        h = 3*h+ 1 # in the test use 4 as increment sequence
    while h >= 1:
        for i in range(len(a)):
            j = i
            while j >= h and a[j] < a[j-h]:
                a[j], a[j-h] = a[j-h], a[j]
                j -= h
        h /= 3

def merge_sort(x):
    result = []
    if len(x) < 2:
        return x
    mid = int(len(x)/2)
    y = merge_sort(x[:mid])
    z = merge_sort(x[mid:])
    i = 0
    j = 0
    while i < len(y) and j < len(z):
        if y[i] > z[j]:
            result.append(z[j])
            j += 1
        else:
            result.append(y[i])
            i += 1
    result += y[i:]
    result += z[j:]
    return result

def quick_sort(a):
     if len(a) <= 1:
          return a
     else:
          return quick_sort([x for x in a[1:] if x < a[0]]) + [a[0]] \
                + quick_sort([x for x in a[1:] if x >= a[0]])

if __name__ == '__main__':
    a = [7, 10, 1, 1, 3, 4, 5, 9, 2, 8]
    b = {}
    for i in range(1, 6):
        b['test'+str(i)] = a[:]
    # Test the three simple sortings
    insertion_sort(b['test1'])  #1
    selection_sort(b['test2'])  #2
    shell_sort(b['test3'])      #3
    print b
    # Test the sortings that requires recursion
    print merge_sort(b['test4'])        #4
    print quick_sort(b['test5'])        #5
    # Timsort that is native in Python
    a.sort()                            #6
    print a

​

SAS and VBA (8): What VBA can do for SAS?

The eventual answer is user interface (I really don't mean SAS/AF) with or without Excel.

In the Windows environment, SAS provides data access layer by ADO and ADO.NET. We can build the applications using SAS at the backend through ADO.NET. Since Visual Basic.NET has the similar syntax to VBA, if somebody is comfortable to code in Microsoft's Visual Studio, then developing a desktop app is always feasible although it requires quite a few efforts.

For example, let's run a simple linear regression using the variable of WEIGHT to predict HEIGHT based on the SASHELP.CLASS data set. We can use Excel's macro to fetch the output by SAS as a new result sheet in the same workbook. Similarly, a desktop application (.exe) will run SAS as batch mode without notice and return the regression result in a browser. The best part of developing such an application is that Excel or Visual Studio comes at nearly no cost. And as you know, the client doesn't need to know anything about SAS.

The UI in Excel

After hitting the button

The UI of a desktop app

After hitting the button