Using formulas in Excel and actual values in HTML and PDF output

On a recent project I had to use the technique I described in an earlier post to create HTML, PDF, and Excel (using the ExcelXP tagset) output all at the same time. Adding ExcelXP output was straightforward. But, of course, there was a wrinkle. The Excel version had to use formulas so the user could do some what-if analysis. So I faced what I thought was a serious problem - how to put the actual values in the PDF and HTML versions, but with formulas in Excel.

But luckily the ExcelXP wizards at SAS (and by that I mean Eric Gebhart and Vince DelGobbo) provided a way to do just that! I am not sure if this use-case was part of their design/approach. But it worked. They use the tagattr attribute to do all sorts of cool things - like providing a formula. Here is a simple example that generates (all at once) HTML, PDF and Excel versions of the report with the static values in PDF and HTML, but formulas in Excel.

options nodate nonumber;
ods listing close;
ods html file='\Formulas.html';
ods pdf file='\Formulas.pdf' notoc;
ods tagsets.ExcelXp file='\Formulas.xml';
proc report data = sashelp.class nowd;
 title 'HTML, PDF, and ExcelXP, with Formulas in Excel';
 columns name age sex height weight bmi;
 define bmi / computed format=5.2 style=
   [tagattr='(formula:RC[-1]*703)/(RC[-2]^2) format:0.00'];
 compute bmi;
   bmi = (weight.sum*703)/(height.sum**2);
 endcomp;
run;
ods _all_ close;

A few points about this:

1. Note that the formula uses the Excel R1C1 notation (and it works regardless of whether this reference style is enabled in Excel).
1. RC[-1] uses the value from the same row, one column to the left.
2. RC[-2] uses the value from the same row, two columns to the left.
3. So the formula uses the current row's prior column value (i.e., one column to the left), multiplies it by 703 and then divides that by the square of the value in current row two columns to the left.
2. Since Excel chooses to do its own thing with formats, the format attribute is used to tell Excel how to format the value.
3. Note the use of ^  instead of ** for the exponentiation operator.

Since the program uses the SASHELP.CLASS data, which luckily has the needed components to calculate Body Mass Index (aka BMI), you can run this yourself.