PROC SUMMARY - not just a summary tool

Yea, I know this sounds like an oxymoron. Why would you want to use PROC SUMMARY for anything other than summarizing data?

I would like to describe two examples where you can use PROC SUMMARY to create

1. A tree view menu - much like Windows Explorer does with folders and files.
2. Cascading select tags where the choices are dependent on previous choices.

A Tree View

In chapter 8 of my (upcoming) ebook (SAS® Server Pages: Generating Dynamic Content), there are examples of SAS Server Pages to create a tree view. Each of the examples uses a slightly different technique to create the needed input data set. Consider the data seen in Figure 1  (using the SASHELP.CLASS data set to make it easier for the reader to try out the code below). The data are arranged so the branches and leaves are nested under their respective parents.

For example:

Figure 1. Tree View Data Set



1. The top level nodes are observations 1(Female) and 16 (Male). This is reflected in the variable nodeText.
2. Observation 2 is the first Age value (eleven) for girls. Again reflected in the variable nodeText.
3. Next we see observation 3 for Joyce - an eleven year old girl.
4. And so on for the rest of the girls.
5. Next we can see that observation 17 is the first Age value for boys and Thomas at observaion 18 is the only eleven year old boy.
6. And so on.

This data set was created by a very simple SAS program:

proc summary data = sashelp.class;
 /* create the needed combinations */
 class sex age name;
 types sex
       sex*age
       sex*age*name;
 output out = treeview(drop=_type_ _freq_);
run;
proc sort data = treeview out = treeview;
 /* reorder the data so the branches and
    leaves are below the parent node */
 by sex age name;
run;
data treeview;
 /* create the menu node text */
 set treeview;
 format age words.;
 nodeText =
    coalescec(vvalue(name),vvalue(age),vvalue(sex));
run;

Figure 2. Tree View

The treeNodes SAS Server Page macro, when referenced in a SAS Server Page, will create the expanding/collapsing menu seen (and partially expanded) in Figure 2.

And two other points about the above code:

1. Notice the use of the coalescec function to get the value of the most detailed branch/leaf.
2. The vvalue function is used to return the formatted value. The use of this function, even if the variables don't have an assigned format, makes sure that all the arguments to the coalesec function are character (thus avoiding those pesky numeric to character conversion messages).

Cascading Select Tags

Conceptually a cascading select tag is simple. Each choice subsets the items in the next (or dependent) list to only include the dependent values for the current selection.

Creating such a data set is even easier that what is shown above. You just have to run the PROC SUMMARY step:

proc summary data = sashelp.class;
 /* create the needed combinations */
 class sex age name;
 types sex
       sex*age
       sex*age*name;
 output out = dependentSelect(drop=_type_ _freq_);
run;

Figure 3. Cascading Select Tag Data

Upon looking at the output data set (Figure 3), it is easy to see that the the various select tags can be constructed as follows:

1. Observations 1 and 2 are the values for the Sex select tag.
2. Observations 3 thru 7 are the values for the Age select tag when the selected value for Sex is F (note that there is no row for Age=16).
3. Observations 8 thru 13 are the values for the Age select tag when the selected value for Sex is M (note that there is a row for Age=16)
4. Observation 14 is the only value for the Name select tag for eleven year old girls.
   . . . .
5. Observations 24 thru 26 are the values for the Name select tag for twelve year old boys.
6. and so on . . . .

And, it should go without saying that the above code can be parameterized as a utility macro. But that is a topic for a future blog posting. As is a utility macro to generate the dependent select tags.

Error Handling in Utility Macros

Dealing with errors is always problematic. It can be particularly challenging in utility macros. Does every macro need to check for every possible error? Or should a developer assume some level of error checking has been done by the calling application, program or macro?

My view is that assuming a certain level of error checking by the calling application, program or macro is reasonable; but that one should still do a reasonable level of defensive programming.

For example, consider the generateOptionTag macro that I have mentioned in a number of posts. It creates a select tag from data in a SAS data set. But suppose the data set, or the variables for the coded value, or the label for the select tag, don't exist?

The minimalist defensive solution is to just exit the macro without generating the select tag. But the user is left with a user interface page that is incomplete/wrong. And since the user, in this case, is almost certainly not a developer, determining exactly what additional messages or diagnostics are appropriate can be challenging.

The approach that I like is to make sure the user knows that an error happened and, at the same time, give them enough information so they can alert the right folks.

Both the Stored Process Server and SAS/IntrNet Application Server programs will display a message to the user if an error is encountered. But since defensive programming has prevented a SAS error, the issue is how can we force that message without generating a SAS error? Reviewing the SAS knowledge base article Tips to remove the Show SAS log button if a SAS® Stored Process executes with an error suggests a simple solution: set the value of the SYSCC macro variable to a value larger that 4.

But we still need to make it easy for the user to find the error. That is where Using NOTE, WARNING, ERROR in Your Program's Generated Messages can help.  You can produce an error message that provides some details and since it will be highlighted in the SAS Log, it is easy to find.

So here is a typical snippet of code that illustrates this technique:

%let syscc=256;
%put ERROR: The specified data set, &data, is not available.;
%return;

The %return statement stops the currently executing macro and returns control to the calling macro or program.

And in a SAS Server Page utility macro that is generating a user interface, you can also generate a mailto hyperlink to make it easy for the user to report the error. Something like:

Email the <a href="mailto:developer-email?Subject=Error Message">developer</a> about the error.

can make it easy for the user to report the error.

Remembering User Choices

In my Chapter 2 of my ebook (SAS® Server Pages: Generating Dynamic Content) I show several examples of reporting functionality that use Stored Processes and SAS Server Pages on the SAS Portal where the user's choices are saved. As shown in the example screen shot, upon logging into the Portal and then selecting the reporting tab, the user's last report choice is remembered and submitted. The box on the left is a select tag (generated by the generateOptionTag utility macro) that uses the size option so multiple choices are displayed. This select tag is populated using the parms.report_list_view data set. The variable Report_Key is the value of the select tag and Report_Description is the label. Thus, remembering the user's choice is simply a matter of saving the value of Report_Key on the server.

The following simple data step saves the user's report choice so that it is available later in the same browser session; as well as the next time they log in (even if it is days later).

data profiles.&_metauser._report;
  Report_Key=&Report_Key;
run;

This code is included in the report driver macro so that whenever the user makes a choice, it is saved. The Stored Process Server provided macro variable _metauser is used to identify the user (note that if the SAS/IntrNet Application Dispatcher is being used, _rmtuser would be used instead of _metauser).

Using the following utility macro in the SAS Server Page that generates the UI allows the page to remember the user's last choice.:

%macro getReportKey;
 %local dsid rc;
 %let dsid=%sysfunc(open(profiles.&_metauser._report));
 %if &dsid=0 %then %return; /*no saved choices dataset*/
 %let rc=%sysfunc(fetch(&dsid));
 %if &rc=0 %then %sysfunc(getvarn(&dsid,1)); /* data set has a saved choice */
 %let dsid=%sysfunc(close(&dsid));
%mend getReportKey;

And then you only need to include code like what is shown below in the SAS Server Page:

%let Report_Key = %getReportKey;
.
.
.
%generateOptionTag
        (data = parms.report_list_view
        ,var = _report_key
        ,name = report_key
        ,selected = &report_key
        ,label = report_description
        ,otherOptions = size=30 onchange="submit();"
        )

That is really all there is to it.

Needless to say there are lots of variations and extensions of this technique:

• This example has a dedicated data library (profiles) that has one data set for each user. A database or SAS/SHARE could be used so that the data is stored in a single data set with a row for each user.
• Multiple data sets can be saved for each user if different kinds of data, or multiple values, need to be saved (in a future blog posting I will discuss the example in my book that uses multiple checkboxes, instead of a select tag). And, of course, there are any number of alternative data structures that could be used.
• If the user id (_metauser) has special characters in it (e.g., /, \ or @), some siimple macro logic could be used to map it to a unique value that is a valid SAS name.
• The macro could be paramaterized (I kept it simple for this example).

I have also used this technique with Web Report Studio using an Information Map as discussed in this example so that the user's choices are remembered for Web Report Studio reports. This can be particularly helpful when there are multiple reports and users don't want to have to reselect the report parameters for each report.

A sample Dual Listbox Selector SAS Server Page

In my Section 5.3.4 of my ebook (SAS® Server Pages: Generating Dynamic Content) which describes the generateOptionTag utility macro, I reference this blog entry as an example of using  a dual listbox selector to allow for multiple selections.  See the example screen shot (at right) that uses the sashelp.class data set.

This sample SAS Server Page makes use of a number of features of PROC STREAM, some JavaScript code, and two utility macros. Before I describe the details of this sample, please feel free to give it a try on my demo server using the Stored Process Server (the example also works for the SAS/InterNet Application Dispatcher):

http://demos.hcsbi.com:8080/SASStoredProcess/guest?_program=/sspebook/sasServerPage&page=dualListBox

To move items you can either:

• double click them
• single click and then use the arrows to move the selected items
• move them all use the double arrows
• you can also move a selected up or down in the list

Then click the Review Name/Value Pairs button to see a list of the items in each box.

Now lets take a quick look at this sample SAS Server Page. Note that this is just a sample. It might make sense to create a template (or a macro) that can have parameters passed to it. For now, I wanted to keep it simple, thus this sample.

The readfile option of the &streamDelim facility of PROC STREAM is used to define the JavaScript routines that are used to move the items. We use readfile here so the text is included in the page rather than assuming it is available on the web server.

Next we set up the form. Note the following:

• Two macro variables are defined since we will need to refer to the names of the left (i.e., Available in this case) and right (i.e., Selected in this case) select tags numerous times.
• The onSubmit JavaScript hander is used to make sure all the items in both select tags are selected.
• The _debug value of 1 generates the display of the name value pairs passed to the stored process.

Use an HTML table to control the layout of the two select tags and the icons/images that are used to move items.

Use the getImage utility macro (discussed in a previous blog, The getImage macro: embedded images using text strings) to generate the images for the up and down arrows in the first table column.

The generateOptionTag macro is used to create the left hand select tag in the second table column. It is populated from the students in the SASHELP.CLASS data set who are 13 years old or younger. Note that the otherOptions parameter of the macro is used to pass in extra paramaters to be included on the select tag:

• The size option to specify that the select tag should be 20 rows long
• The mulitple option to specify that multiple selections are allowed (part of the point of a dual list list box selector)
• A style attribute to define the width
• the onDblClick JavaScript handler so that when a user double clicks on an item it is moved from the left select tag to the righ select tag.

The getImage macro is used to generate the images/icons (in the third table column) used to move items:

• Move all the items from the left to the right select tag
• Move only the selected items from the left to the right select tag
• Move only the selected items from the right to the left select tag
• Move all the items from the right to the left select tag

Just as for the left select tag, the generateOptionTag macro is used to create the right hand select tag in the second table column. It is populated from the students in the SASHELP.CLASS data set who are older tha 13.

The last column is similar to the first one - except the the Move Up and Move Down images are for the right hand select tag

The final bit of HTML closes the table, generates the submit button, closes the form and the HMTL page.

I plan to write a future blog posting to generalize/parameterize this functionality and will include a link to a zip file of the components. In the meantime, please feel free to comment and ask questions about this example.

The getImage macro: embedded images using text strings

A little known HTML trick is that you can embed images in an HTML file using a Base 64 representation of an image. For example, this folder open image:

is generated using the following text in an HTML page:

<img src="data:image/png;base64,R0lGODlhEAAQAKL/AP//////
AMDAwMDAwICAgICA\AAAAAAAAACH5BAEAAAIALAAAAAAQAB
AAAANFKEpMpjAKAkYYTkJqu2sbII6kSCzApXbqM1VrPLiUWpYGFwB
F7/+51ID3KwaHReNrlwS+iM0CTRTtTavSjWHL7W41YE0CADs=">

There are a number of reasons why you, as a developer, might want to use base 64 encoding of images in your web pages, for example:

• you don't have control over the images directory of your web server and you can't add images.
• you don't want to have to deal with the actual url path to the image when creating an html file (either static or dynamic).
• this is a bigger issue for html files not served via a web server (e.g., they are access via the file system and thus easily moved around)
• minimizing round trips to the server when the page is accessed

There are a number of web sites that you can use to convert an image to its it base 64 encoded value. A simple search will find any number of tools you can use to do this. For example:

https://www.google.com/search?q=base64+encoded+image

As a best practice you should limit the use of base 64 encoded images to small images where the base 64 text is not too long. Note that lines feeds are allowed in the HTML text).

The getImage macro is a utility macro designed to be used to generate such images in SAS Server Pages. See my SAS Press e-book, SAS® Server Pages: Generating Dynamic Content, for more information about SAS Server Pages. Just use the macro in a SAS Server Page as follows to insert an image:

• <img src="%getImage(image=OpenFolder)">

You can download a zip file containing the macro, along with:

• SAS transport file of a data set containing a number of sample images
• a sample program illustrating the use of the macro

My next blog entry will use this macro in a sample dual listbox SAS Server Page.