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.

Answer 1

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;