topic Re: EXCEL Formula by TAGATTR in ODS and Base Reporting

EXCEL Formula by TAGATTR

Sylas — Wed, 27 Feb 2013 20:03:33 GMT

Hi,

I am using the following below code for getting excel data as formula in the sheet but i am not getting the formulas.

ods tagsets.ExcelXP path='C:\Documents and Settings\Excel' file="Sample.xml"
options(embedded_titles='yes' SUPPRESS_BYLINES='yes' SKIP_SPACE='1,0,0,1,1' sheet_interval="none" )
style=styles.Default;

proc report data=sample;
column product product_Avg;
define product_Avg / style={tagattr='format:0% formula:B3/SUM(B$3:B$9)'};
run;

ods tagsets.ExcelXP close;
ods _all_ close;

Can anybody help me to get excel formulas.

Regards
Sylas.J

Re: EXCEL Formula by TAGATTR

Cynthia_sas — Wed, 27 Feb 2013 21:11:40 GMT

Hi:

I'm not an power-user of Excel, however all the times I have made formulas work, it has been with RC notation and not with notation such as you show. I use the example from this web site: Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

If you have trouble figuring it out, then I would recommend working with Tech Support. Below is a simple example that I can offer. Profit is not in SASHELP.SHOES and is calculated by multiplying SALES by .9 using a formula passed to Excel.

cynthia

ods tagsets.excelxp file='c:\temp\useformula.xml'

options(doc='Help')

style=sasweb;

proc report data=sashelp.shoes(obs=10) nowd ;

title 'Using tagattr and TAGSETS.EXCELXP';

title2 'Send a Formula for a Column';

column region product sales profit;

define region/display;

define product /display;

define sales/ sum 'Sales'

style(column)={tagattr="$###,##0.00"};

define profit / computed "Profit"

style(column)=

{tagattr='Formula:RC[-1]*0.9'};

compute profit;

** profit is not in sashelp.shoes;

** so make an empty column for the formula;

profit = 0;

endcomp;

run;

ods tagsets.excelxp close;

Re: EXCEL Formula by TAGATTR

Sylas — Wed, 27 Feb 2013 21:44:56 GMT

Hi Cynthia,

Thanks for your solution , i changed my formula as of RC notation as you said it worked well.

Regards

Sylas.J