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=
bmi = (weight.sum*703)/(height.sum**2);
ods _all_ close;
A few points about this:
- Note that the formula uses the Excel R1C1 notation (and it works regardless of whether this reference style is enabled in Excel).
- RC[-1] uses the value from the same row, one column to the left.
- RC[-2] uses the value from the same row, two columns to the left.
- 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.
- Since Excel chooses to do its own thing with formats, the format attribute is used to tell Excel how to format the value.
- Note the use of ^ instead of ** for the exponentiation operator.