topic Re: Embedding Excel Formals in ODS and Base Reporting

Embedding Excel Formals

RobertNYC — Wed, 30 May 2012 13:20:55 GMT

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!

Re: Embedding Excel Formals

RobertNYC — Wed, 30 May 2012 13:24:28 GMT

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)

Re: Embedding Excel Formals

Cynthia_sas — Wed, 30 May 2012 14:05:16 GMT

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

Re: Embedding Excel Formals

RobertNYC — Wed, 30 May 2012 15:00:12 GMT

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!

Re: Embedding Excel Formals

Cynthia_sas — Wed, 30 May 2012 16:03:52 GMT

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