BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

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

8 REPLIES 8
Reeza
Super User

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.

SASKiwi
PROC Star

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.

rogerjdeangelis
Barite | Level 11
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.

Vince_SAS
Rhodochrosite | Level 12

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)

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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