EXCEL Formula by TAGATTR

Solved
Occasional Contributor
Posts: 17

EXCEL Formula by TAGATTR

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

Accepted Solutions
Solution
‎02-27-2013 04:11 PM
SAS Super FREQ
Posts: 9,368

Re: EXCEL Formula by TAGATTR

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;

All Replies
Solution
‎02-27-2013 04:11 PM
SAS Super FREQ
Posts: 9,368

Re: EXCEL Formula by TAGATTR

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;

Occasional Contributor
Posts: 17

Re: EXCEL Formula by TAGATTR

Posted in reply to Cynthia_sas

Hi Cynthia,

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

Regards

Sylas.J

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 2 replies
• 2527 views
• 1 like
• 2 in conversation