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;
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)
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
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.
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.
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.
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;
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.
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
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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.