topic Re: Using Formulas with ODS EXCEL ends up in a damaged excel file in ODS and Base Reporting

Using Formulas with ODS EXCEL ends up in a damaged excel file

anonymous_user — Sun, 25 Dec 2016 13:29:25 GMT

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;

Re: Using Formulas with ODS EXCEL ends up in a damaged excel file

Reeza — Sun, 25 Dec 2016 14:45:07 GMT

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

Re: Using Formulas with ODS EXCEL ends up in a damaged excel file

SASKiwi — Mon, 26 Dec 2016 05:38:18 GMT

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.

Re: Using Formulas with ODS EXCEL ends up in a damaged excel file

anonymous_user — Mon, 26 Dec 2016 12:47:47 GMT

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.

Re: Using Formulas with ODS EXCEL ends up in a damaged excel file

anonymous_user — Mon, 26 Dec 2016 13:00:30 GMT

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.

Re: Using Formulas with ODS EXCEL ends up in a damaged excel file

rogerjdeangelis — Tue, 27 Dec 2016 19:02:18 GMT

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;

Re: Using Formulas with ODS EXCEL ends up in a damaged excel file

Vince_SAS — Tue, 27 Dec 2016 20:24:34 GMT

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

Re: Using Formulas with ODS EXCEL ends up in a damaged excel file

anonymous_user — Wed, 28 Dec 2016 08:31:28 GMT

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.

Betreff: Using Formulas with ODS EXCEL ends up in a damaged excel file

anonymous_user — Sat, 07 Jan 2017 18:12:37 GMT

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)