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.