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
- /
- Using Formulas with ODS EXCEL ends up in a damaged...

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

12-25-2016 08:26 AM - edited 12-25-2016 08:29 AM

Hi,

I have a problem using formulas with ODS EXCEL. I'm trying to implement a chain-ladder method using formulas in my excel output. I don't want to calculate the future values in SAS, because the calculation method should be documented within the excel file. Everything works perfectly, but there seems to be a general problem using formulas with ODS EXCEL.

If I reference another column in the same row ( e.g. RC[-1] ) it just works. A reference to another row in the same column ( e.g. R[-1]C ) also works without problems. But as soon as I reference another row and another column ( e.g. R[-1]C[-1] ) this ends up in a damaged excel file. Unfortunately this kind of formula is what I need for the chain-ladder method.

I tried the same with ODS TAGSETS.EXCELXP and there is no problem with formulas like R[-1]C[-1], it just works perfectly. Unfortunately I can't switch to TAGSETS because I'm using some features that are only available with ODS EXCEL (e.g. writing output to different sheets).

Does anyone know what I'm doing wrong or if this is a known bug in ODS EXCEL?

Here some simple code to reproduce the problem. Try the three different kinds of formulas by commenting the other two lines, the last one ends up in a damaged excel file. Change ODS EXCEL to ODS TAGSETS.EXCELXP and everything works.

```
data work.test;
set sashelp.class;
attrib age2 format=3. label="Age2";
age2 = 0; * default value - formulas are not created in cells with missing values;
keep name age age2;
run;
ods excel file="/folders/myfolders/formula_test.xlsx" style=htmlBlue;
proc print data=test noobs label;
id name;
var age / style={TAGATTR='format:0'};
var age2 / style={TAGATTR='format:0 formula:RC[-1]'}; * this works;
*var age2 / style={TAGATTR='formula:R[-1]C'}; * this works;
*var age2 / style={TAGATTR='format:0 formula:R[-1]C[-1]'}; * this ends up in a damaged excel file;
run;
ods excel close;
```

Accepted Solutions

Solution

01-07-2017
12:55 PM

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

Posted in reply to stgt800

01-07-2017 12:49 PM - edited 01-07-2017 01:12 PM

This week I had contact to SAS Technical Support. The answer was that this is an already known issue, but unfortunately there's no hotfix available yet. Also SAS 9.4 M4 doesn't fix it.

By now I found two possible workarounds to use until it is fixed:

1. Use ODS TAGSETS.EXCELXP instead of ODS EXCEL as long as there's no need for any features that are exclusively available for ODS EXCEL (e.g. embedding graphics).

2. With ODS EXCEL it is also possible to create the formulas by using the INDIRECT functionality of excel. Unfortunately this leads to longer and more complex formulas. The formulas created in excel are also not as easy to understand as a normal direct formula, because for indirect formulas excel will not highlight the cells used for the calculation.

In my case I would normally use:

`'format:0.00% formula:MAX(RC[-1]+AVERAGE(R[-4]C:R[-1]C)-AVERAGE(R[-4]C[-1]:R[-1]C[-1]),0)'`

For e.g. cell C6 excel would show the following formula

=MAX(B6+AVERAGE(C2:C5)-AVERAGE(B2:B5),0)

With the indirect formula it is now:

```
'format:0.00% formula:MAX(INDIRECT(ADDRESS(ROW(),COLUMN()-1))
+AVERAGE(INDIRECT(ADDRESS(ROW()-4,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
-AVERAGE(INDIRECT(ADDRESS(ROW()-4,COLUMN()-1)):INDIRECT(ADDRESS(ROW()-1,COLUMN()-1))),0)'
```

For e.g. cell C6 excel shows now the following formula

=MAX(INDIRECT(ADDRESS(ROW(),COLUMN()-1))+AVERAGE(INDIRECT(ADDRESS(ROW()-4,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))-AVERAGE(INDIRECT(ADDRESS(ROW()-4,COLUMN()-1)):INDIRECT(ADDRESS(ROW()-1,COLUMN()-1))),0)

All Replies

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

Posted in reply to stgt800

12-25-2016 09:45 AM

ODS tagset also supports multiple sheets.

ODS EXCEL also supports formulas that are in the standard Excel format, ie E1 references.

https://support.sas.com/resources/papers/proceedings16/SAS5642-2016.pdf

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

Posted in reply to Reeza

12-26-2016 07:47 AM

Thank you. Your are right, I completely forgot that it was already possible to create multiple sheets with ods tagsets. I will have to check if there is any other reason that prevents me to use ods tagsets instead of ods excel.

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

Posted in reply to stgt800

12-26-2016 12:38 AM

What SAS version are you using? I'm pretty sure ODS Excel only went production with 9.4M3. M4 has just been released and might be worth checking out to see if it fixes your problems.

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

Posted in reply to SASKiwi

12-26-2016 08:00 AM

I think it is 9.4M3. I just had a look at the release notes of M4 and it seems that some other bugs I detected with ods excel have also been fixed with this release. Perhaps it is also the case for the excel formulas. I will to talk to our IT people when I'm back in the office.

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

Posted in reply to stgt800

12-27-2016 02:02 PM

```
SAS Forum: Using Formulas with ODS EXCEL ends up in a damaged excel file
You might get a much faster response if you cross post to SAS-L
inspired by
http://tinyurl.com/z8mt4uo
https://communities.sas.com/t5/ODS-and-Base-Reporting/Using-Formulas-with-ODS-EXCEL-ends-up-in-a-damaged-excel-file/m-p/321085
HAVE excel sheet
+-------------------------+
| EXCEL| A B |
| | |
| ROW | NAME AGE |
| | |
| 1 | Alfred 14 |
| 2 | Alice 13 |
| 3 | Barbara 13 |
| 4 | Carol 14 |
| 5 | Henry 14 |
+-------------------------+
WANT
+--------------------------------+
| EXCEL| A B C |
| | |
| ROW | NAME AGE AGE2 |
| | |
| 1 | Alfred 14 14 |
| 2 | Alice 13 13 |
| 3 | Barbara 13 13 |
| 4 | Carol 14 14 |
| 5 | Henry 14 14 |
+--------------------------------+
SOLUTION (Win 7 64 SAS9.4M2)
%utlfkil(d:\xls\formulax.xlsx); * delete;
ods excel file='d:\xls\formulax.xlsx';
proc report data=sashelp.class(keep=name age);
column name age age2;
define age2 / "AGE2"
computed format=3. style={tagattr="formula:(RC[-1])"};
compute age2;
age2=0;
endcomp;
run;
ods excel close;
```

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

Posted in reply to rogerjdeangelis

12-28-2016 03:31 AM

Thank you, but there is no problem with formulas like RC[-1]. I tried your code with the formula R[-1]C[-1] and it also leads to a damaged excel file.

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

Posted in reply to stgt800

12-27-2016 03:24 PM

Please open a ticket with our Technical Support Department:

http://support.sas.com/ctx/supportform/createForm

The problem seems to be only partially corrected in the fourth maintenance for SAS 9.4.

Vince DelGobbo

SAS R&D

Solution

01-07-2017
12:55 PM

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

Posted in reply to stgt800

01-07-2017 12:49 PM - edited 01-07-2017 01:12 PM

This week I had contact to SAS Technical Support. The answer was that this is an already known issue, but unfortunately there's no hotfix available yet. Also SAS 9.4 M4 doesn't fix it.

By now I found two possible workarounds to use until it is fixed:

1. Use ODS TAGSETS.EXCELXP instead of ODS EXCEL as long as there's no need for any features that are exclusively available for ODS EXCEL (e.g. embedding graphics).

2. With ODS EXCEL it is also possible to create the formulas by using the INDIRECT functionality of excel. Unfortunately this leads to longer and more complex formulas. The formulas created in excel are also not as easy to understand as a normal direct formula, because for indirect formulas excel will not highlight the cells used for the calculation.

In my case I would normally use:

`'format:0.00% formula:MAX(RC[-1]+AVERAGE(R[-4]C:R[-1]C)-AVERAGE(R[-4]C[-1]:R[-1]C[-1]),0)'`

For e.g. cell C6 excel would show the following formula

=MAX(B6+AVERAGE(C2:C5)-AVERAGE(B2:B5),0)

With the indirect formula it is now:

```
'format:0.00% formula:MAX(INDIRECT(ADDRESS(ROW(),COLUMN()-1))
+AVERAGE(INDIRECT(ADDRESS(ROW()-4,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))
-AVERAGE(INDIRECT(ADDRESS(ROW()-4,COLUMN()-1)):INDIRECT(ADDRESS(ROW()-1,COLUMN()-1))),0)'
```

For e.g. cell C6 excel shows now the following formula

=MAX(INDIRECT(ADDRESS(ROW(),COLUMN()-1))+AVERAGE(INDIRECT(ADDRESS(ROW()-4,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN())))-AVERAGE(INDIRECT(ADDRESS(ROW()-4,COLUMN()-1)):INDIRECT(ADDRESS(ROW()-1,COLUMN()-1))),0)