excel formula not updating after sas export

Question

I'm running a SAS program that updates two sheets in an Excel workbook. The workbook contains a third sheet that is just formulas pointing to cells in the SAS exported sheets. Whenever I rerun the program with new values, the formulas on sheet 3 don't update their values.

An example would be this: sheet1 a1 = 2, sheet2 a1 = 3, sheet3 a1 = sheet1!$a$1+sheet2!$a$1 (shows 5) I rerun the SAS export changing the first two values to 3 and 4, but sheet3 still shows 5 instead of 7.

When I click on the sheet3 cell and evaluate the formula, the first two values appear correct as 3 and 4. The third evaluation of 3+4 shows as 5 still. If I copy/paste the formula into a different cell, the correct calculation is done and 7 is shown. If I cut/paste the formula to another cell, the incorrect value shows, but if I copy/paste from there back to the original cell, the correct value shows.

I have Excel 2013, with automatic calculations set, and SAS 9.3 all running on Windows 7 and all 64-bit.

I would suggest trying to separate the calculation sheet into a different workbook from the SAS produced data. Updating across workbooks might be superior to updating inside a workbook in this instance. — Joe
We went a different route but this solution does work. When I reopen the formula workbook I'm asked if I want to update links and after I select 'Update' the new values are calculated correctly. — user3112382
FYI, the new ods excel (available in the next month or so with tne updated release of 9.4) likely will make this sort of approach necessary; it will mean that you can export the formulas themselves to a true Excel file, so you don't have to have a template workbook with the formulas in them. — Joe

Stig Eide Stig Eide · Accepted Answer · 2014-01-15T09:51:49

With ODS you can create Excel formulas:

ODS TAGSETS.EXCELXP
file="c:\temp\shoes.xml"
STYLE=minimal
OPTIONS ( Orientation = 'landscape'
FitToPage = 'yes'
Pages_FitWidth = '1'
Pages_FitHeight = '100' );

proc print noobs data=sashelp.shoes;
run;

ods tagsets.excelxp close;

excel formula not updating after sas export

1 Answers