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
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;
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;
Hi Cynthia,
Thanks for your solution , i changed my formula as of RC notation as you said it worked well.
Regards
Sylas.J
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.