Friday, August 23, 2013

SAS Server Pages in Batch - Adding a BY variable to the tagCloud macro

In my last post, SAS Server Pages in Batch? Absolutely!, I talked about how SAS Server Pages could be used in batch (e.g., SAS Display Manager, SAS Enterpreise Guide, scheduled SAS processes, etc.). I presented a macro, tagCloud, that could produce a graphical display of BI data.

This post extends the macro to include a single BY variable so a separate tag cloud is produced for each value of the BY variable. This example macro produces a single HTML page with multiple tag clouds. The macro could easily be modified to produce a separate HTML for each value of the BY variable. I will leave that as an exercise for the reader ;-).

A sample macro call, along with the complete source for the macro is included below, with the changes highlighted in red.

First, here is the macro call. Assuming you save the tagCloud macro in a SAS autocall library, you can produce tag clouds with these two lines of code. In my last example I produced a tag cloud where the variable Subsidiary was the CLASS variable. Here I am producing a separate tag cloud for each Region, with Product used as the CLASS variable.

filename _webout "&root\Output\TagCloud-By-Region.html";

First, three simple additions to the macro:
  1. A macro parameter for the BY variable is added. It defaults to null. If not provided, the macro produces the same output shown in SAS Server Pages in Batch? Absolutely!.
  2. Added a few macro variables to our local list - a Best Practice to make such utility macro variables local.
  3. Add the BY variable to the summary step so we have the statistic calculated for each combination of the BY and CLASS (the word macro parameter) variables.
%macro tagCloud(data =  /* input data */
               ,by =                  /* by variable to use */
               ,where = 1             /* subset where clause */
               ,word = Subsidiary     /* class variable */
               ,weight = Sales        /* analysis variable */
               ,statistic = Sum       /* statistics to calculate */
               ,outfile = _webout     /* output fileref */
               ,width = 600px         /* width of the tag cloud */
               ,height = 450px        /* height of the tag cloud */

 %local byTitle numByGroups pageTitle pageHeader nl;

 proc summary data = &data nway;
  where &where;
  class &by &word;
  var &weight;
  output out=cloud(drop=_type_ _freq_) &statistic=;

If a BY variable is specified, get the list of distinct values and populate values for:
  1. numByGroups - note that a value of 1 is assigned if there is no BY variable specified. We need to generate just one tag cloud.
  2. pageTitle - the text for the HMTL TITLE tag. A macro variable is used since the values depends on whether or not a BY variable is specified and what the value of the BY variable is.
  3. pageHeader - Some text to use for a title/banner at the top of the page. Note that a value is only provided if there is a BY variable.
 %if %length(&by) gt 0 %then
 %do;  /* produce a separate tag cloud for each value of the by variable */
    proc sql noprint;
     select distinct &by into :by1-:by999 from cloud;
    %let numByGroups = &sqlobs;
    %let pageTitle = Tag Clouds for each &by for the &statistic of &weight by &word;
    %let pageHeader = <h3>&pageTitle</h3>; 
    %let numByGroups = 1;
    %let pageTitle = Tag Cloud for the &statistic of &weight by &word;

Call PROC STREAM to create the  HEAD section of the HTML (top_matter in SAS ODS terminology) along with the beginnings of the BODY section. The macro variable pageTitle is used to provide the value for the value for the HTML title attribute

 proc stream outfile = &outfile quoting = both;
%let nl = &streamDelim newline;
&nl; <head>
&nl;  <title>&pageTitle</title>
&nl;  <script src="&GoogleApiLib/jquery/1.10.1/jquery.min.js"> </script>
&nl;  <script src="&GoogleApiLib/libs/jqueryui/1.10.3/jquery-ui.min.js"></script>
&nl;  <style>&streamDelim readfile srvrpgs(jqcloud.css);</style>
&nl;  <script>&streamDelim readfile srvrpgs(jqcloud-1.0.3.js);</script>
&nl; </head>
&nl; <body>

Add the pageHeader text (note that the value is null if there is no BY variable). Then end the current PROC STREAM invocation as we need to use a macro loop to use PROC STREAM generate the HTML and JavaScript for each BY group.


First reassign the output fileref so additional content is appended to it. Without the mod option, the file would be over-written.Then use a macro %DO loop to execute PROC STREAM once for each value of the BY variable. If there is no BY variable we still execute the code in the loop once.
  • The macro parameter, where, is re-used to specify the where clause for the current by group.
  • The byTitle macro variable contains the text for the current by group (it's use is described below).
  • Note that a where clause of 1 (always true) is used if there is no BY variable
  • Likewise, the byTitle macro variable contains the same text (&pageTitile) as is used for the HTML TITLE tag when there is no BY variable.

    filename &outfile "%sysfunc(pathname(&outfile))" mod;
    %do i = 1 %to &numByGroups;
       %if %length(&by) gt 0 %then
          %let where = &by="&&by&i";
          %let byTitle = &&by&i;
          %let where = 1;
          let byTitle = &pageHeader;


       proc stream outfile = &outfile quoting = both;
%let nl = &streamDelim newline;

There are only a few changes in the HTML and JavaScript code:
  1. A margin attribute is used to provide some space so the regions for the tag clouds have some white space between them. Note that the float:left allows the output to wrap to a new row only when needed.
  2. The byTitle macro variable (defined above) is used to provide the title text displayed in the border of each tag cloud.
  3. In order for the id value for the region used for the tag cloud, as well as for the name of the JSON variable containing the tag cloud data, to be unique, we suffix the name with &i.
&nl;<div style="float:left; width:&width; height:&height; margin:5px;">
&nl;<fieldset><legend style="font-size:70%;">&byTitle</legend>
&nl;<div id="cloudCanvas&i" style="width:&width; height:&height;"></div>
&nl; var tagCloud&i
&nl; %tagCloudJSON(data=cloud
&nl; $(function() {
&nl;   $("#cloudCanvas&i").jQCloud(tagCloud&i);
&nl; });

After exiting the macro %DO loop we close use PROC STREAM to write the footer text and HTML (e.g., bottom_matter in SAS ODS terminology).

 proc stream outfile = &outfile quoting = both;
%let nl = &streamDelim newline;
&nl;<div style="clear:both; font-size:70%;">%generatedAt()</div>
&nl; </body>
%mend tagCloud;

And for those of you who want to download the code, I have a few more examples to blog about. Once I do that, I will package up all the pieces and parts (e.g., the AJAX container, the tag clouds SAS Server Pages, the macros, etc.) as a zip file.