Monday, April 9, 2012

A Sample Mail-Merge Application

So far all of my postings about PROC STREAM and SAS Server Pages have been setting up some of the basics. This post is going to be about a real-world use case: a mail-merge example where the requirement is to generate custom content for each defined subset of data—usually each observation. This example is based on the example from Section 4.1 of SAS® Server Pages: Generating Dynamic Content (and Chapter 4 is included in the soon to be released free preview copy). It has been slightly modified so it can be run over the web. That way you can see the results using these links:
If no observation number is specified, the default is to generate a letter for observation 1. But you can supply an observation number as follows:
The value of the parameter letterObs is checked to make sure it is an integer as discussed in my last post, Simple Utility Macros and SAS Server Pages. Feel free to try different observation numbers by editing the URL.

Assuming you've clicked on the links to try these out, lets talk about how this works. The code is being run by my runMacro facility that runs the following macro.

%macro singleLetter;

%global letterObs;
%let letterObs = %verifyInteger
                    (value=&letterObs
                    ,min=1
                    ,max=19
                    ,default=1
                    );

proc format;
 /* create a format that maps the value
 of sex to daughter/son
 */
 value $gender 'F' = 'daughter'
               'M' = 'son'
 ;
run;

data _null_;
 /* read one observation and create macro variables.
    note the use of the vvalue function to
    cause the formatted values to be used.
 */
 set sashelp.class(firstobs=&letterObs obs=&letterObs);
 /* associate the desired formats with sex and age */
 format sex $gender. age words12.;
 call symputx('Name',vvalue(Name));
 call symputx('Sex',vvalue(Sex));
 call symputx('Age',vvalue(Age));
 call symputx('Height',vvalue(Height));
 call symputx('Weight',vvalue(Weight));
 stop;
run;

proc stream outfile=_Webout sqac;
/* %include the input SAS Server Page */
BEGIN
&streamDelim;%include srvrpgs(class.html);
;;;;
run;

%mend singleLetter;

The code is pretty simple at a conceptual level:
  • A format is created to map F/M to daughter/son so that the generated letter looks a little nicer.
  • DATA Step is used to subset the desired observations and load all the variables into like-name macro variables as well as to assign formats to both the Age and Sex variables. Note that the SYMPUTX function is used as it handles numeric to character conversion. The VVALUE function is used so the values of the macro variables are the formatted values.
  • Then  PROC STREAM is called and the input SAS Server Page which is the desired HTML letter is defined using %INCLUDE.
And, here is the input SAS Server Page that PROC STREAM processes:

<html>
<head>
<title>&Name</title>
</head>
<body>
%sysfunc(date(),worddate.)
<br><br>
<b>Dear Parents</b>:
<p>As &name's teacher I wanted to make you
 aware of a project we are doing this year.
<p>Every month we will be collecting your &age
 year old &sex's height and weight and recording
 it. At year end, &name will be asked to produce
 a chart of their growth over the school year.
<p>For your information, here are the
 measurements that we just collected:
<ul><li><b>Height:</b> &height inches.
<li><b>Weight:</b> &weight pounds.
</ul>
<p>Please don't hesitate to contact me if
 you have any questions,
<p>Regards,
<br><br>
&name's teacher
</body>
</html>

Just a few notes about this HTML file (which I refer to as a SAS Server Page due to the use of the Macro Language to customize it):
  • The %SYSFUNC Macro is used with the DATE function to insert the current date using the worddate format.
  • The desired text is intermixed with the needed HTML tags (e.g., P, BR, UL, LI, B, etc.).
  • The macro variables created in the DATA Step are simply referenced and thus resolved by PROC STREAM.
So, yes, it really can be that simple.

In Chapter 4 I build upon this example by next using the macro facility to generate a letter for each observation in the data set. I then show how to create an RTF document (that can be viewed and edited by word processing softtware like Microsoft Word). My next two blogs will take a different path by building on this example to illustrate:
  1. Embedding SAS code to be executed into a SAS Server Page. That is, the format that creates the $gender format and the code to create the macro variables will be part of the input SAS Server Page.
  2. Creating a SAS Server Page which is a user-interface to the letter generation process - allowing the user to select which letter to generate.