turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- ODS and Base Reporting
- /
- EXCEL Formula by TAGATTR

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-27-2013 03:03 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Sylas

02-27-2013 04:11 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Sylas

02-27-2013 04:11 PM

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;**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cynthia_sas

02-27-2013 04:44 PM

Hi Cynthia,

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

Regards

Sylas.J