Tim-
Thanks for coming to my talk and for your compliment. I haven't seen you in a while; you should have stopped by the demo room to say "Hi".
To answer your question, yes you can pass formulas to Excel, but the notation is a bit odd. You can accomplish this by using the FORMULA attribute of TAGATTR.
Consider this SAS table:
[pre]
data test;
length x y z 8;
input x y;
z = x + y;
cards;
1 4
2 5
3 6
;
run;
[/pre]
Suppose you want column Z to contain a formula in Excel. Here is the corresponding SAS code to accomplish that:[pre]
ods listing close;
ods tagsets.ExcelXP file='test.xml';
proc print data=test noobs;
var x y;
var z / style={tagattr='formula:RC[-2]+RC[-1]'};
run; quit;
ods tagsets.ExcelXP close;
[/pre]
The Excel XML spec uses relative column and row notation for formulas. Here is what Microsoft has to say: "All formulas are persisted in R1C1 notation because they are significantly easier to parse and generate than A1-style formulas".
The above formula is interpreted as follows: add the value in the cell that is two to the left of the current cell to the value in the cell that is one to the left of the current cell.
You can also embed formulas in the SAS table and print that column. If you use that technique, make sure the formula begins with an equal sign (=).
Vince DelGobbo
SAS R&D