The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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

[ Edited ]

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
Occasional Contributor
Posts: 5

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

[ Edited ]

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)

 

View solution in original post


All Replies
Super User
Posts: 19,080

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

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

 

 

Occasional Contributor
Posts: 5

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

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.

Super User
Posts: 3,233

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

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.

Occasional Contributor
Posts: 5

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

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.

Valued Guide
Posts: 505

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

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;

Occasional Contributor
Posts: 5

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

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.

SAS Super FREQ
Posts: 301

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

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
Occasional Contributor
Posts: 5

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

[ Edited ]

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)

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 674 views
  • 0 likes
  • 5 in conversation