turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-22-2006 10:56 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Vince_SAS

04-06-2006 08:06 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to deleted_user

04-10-2006 01:06 PM

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

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