SUGI 31 Presentation on Multi-Sheet Excel Workbooks

[Vince_SAS]

FYI, I'm offering a paper in the Tutorials section as well as a Hands-On Workshop on using ODS to create multi-sheet Excel workbooks.

For dates and times, refer to:

http://sugi31.confnav.com/sugi31/web/sessions/search/detail.html?id=20050927151004339784000000

http://sugi31.confnav.com/sugi31/web/sessions/search/detail.html?id=20050714201536180420000000

Vince DelGobbo
SAS R&D

[deleted_user]

Vince,

I attended your workshop at SUGI. Nice job. I am wondering if we can use the ExcelXP tagset to pass formulas? If so, do you have an example?

Thanks,
Tim

[Vince_SAS]

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