EXCEL Formula by TAGATTR

[Sylas]

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

[Cynthia_sas · Accepted solution]

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;

[Sylas]

Hi Cynthia,

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

Regards

Sylas.J