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

- Home
- /
- SAS Programming
- /
- ODS and Base Reporting
- /
- Embedding Excel Formals

Topic Options

- 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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-30-2012 09:20 AM

Hi all—

I’d like to embed excel formals into a spreadsheet when it generates and I was going to use ExcelXP and proc report. I’m having trouble understanding the documentation – there seem to be a lot of ways to write the code. Below is an example of the finished product I’m trying to produce.

A | B | C | D | E | F | G |

Agency | Regular_Beds | Regular_Beds_not_in_VC | % of Regular Beds not in VC | Beds_in_VC | Beds_Vacant_in_VC | % of FBH Beds Vacant in VC |

Agency ABC | 71 | 26 | 37% | 97 | 16 | 16% |

** =IF(B3=0,0,C3/B3) ** **=IF(E3=0,0,F3/E3)**

Any help will be greatly appreciated!

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

Posted in reply to RobertNYC

05-30-2012 09:24 AM

**Sorry the formulas are these: **

**% of Regular Beds not in VC**

** =IF(B3=0,0,C3/B3) **

**% of FBH Beds Vacant in VC**

**=IF(E3=0,0,F3/E3)**

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

Posted in reply to RobertNYC

05-30-2012 10:05 AM

Hi: Is this the doc you are talking about?

http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html#formats

The main difference is that instead of "A1/B1" notation, you have to switch to RC notation for your formulas, as shown in the link above. Luckily, Excel has options to let you switch, so you can enter your formula as A1/B1 form and then switch to RC in Excel options so you can see what the formula would translate to.

You didn't show your code. Are you using TAGATTR in a style override as shown in the above example? The example shows PROC PRINT, but PROC REPORT would work, as well.

cynthia

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

Posted in reply to Cynthia_sas

05-30-2012 11:00 AM

Hi Cynthia--

I was actually looking for documentation embedding excel formulas using PROC REPORT and TAGATTR style override but I could not find any. Can you recommend something? And yes, the link you provided above was one of the things I was talking about.

Thanks!

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

Posted in reply to RobertNYC

05-30-2012 12:03 PM

Hi:

Well that example and what's in DOC='HELP' is the only thing I've ever used. But then, I don't generally put formulas into my data or into my output. There may be some papers about using formulas with TAGSETS.EXCELXP, but the main thing in my understanding is knowing what the RC notation for the formula is. Well that and knowing WHAT the correct formula is. And both of those 2 pieces of information come from Excel. You seem to already have those...but you don't have the RC notation for your formulas.

And the only way to find out the RC notation is to turn it on in Excel, make a "fake" couple of rows that have the same order of items that you will have in PROC REPORT or PROC PRINT and then copy the RC formula from Excel into SAS. Sort of a pain, I know, but I don't "think" in RC notation, so I always have to reverse engineer the formula.

If I were using PROC REPORT, I probably would NOT bother to use a formula and would do my calculations in a COMPUTE block. If you just want to do something like calculate the difference between height and weight (silly calc) for every row in SASHELP.CLASS, then you could do something like this:

**proc report data=sashelp.class nowd;**

** column sex name age height weight diff1;**

** define sex / order;**

** define name /order;**

** define age / sum;**

** define height / sum;**

** define weight / sum;**

** define diff1 / computed;**

** compute diff1;**

** ** use proc report to compute difference;**

** if weight.sum gt 0 then diff1 = weight.sum - height.sum;**

** else if weight.sum = 0 or weight.sum = . then diff1=0;**

** endcomp;**

**run;**

The only reason that I can think of for using formulas with TAGATTR is if you are giving your users a report where you **want** them to change values and you want to use the formula to change the value in the Excel cell when they type a change. But, I rarely allow folks to change the values of the data that comes from SAS -- in general it violates the integrity of my report. If I send a report out to 10 people (as an ODS output file from TAGSETS.EXCELXP) and 9 of the folks don't change the data and 1 of them does change the data in Excel, then there are 2 versions of my report out in the world and if (usually not if, but when) somebody wants to know why person X has a different report then they do, I have to go back and prove that the original report had different numbers.

So if you really want a TAGATTR solution, you will have to follow the examples in the above link from Tech Support (and use RC notation).

cynthia