sas导出后excel公式不更新 [英] excel formula not updating after sas export

问题描述

我正在运行一个SAS程序，在Excel工作簿中更新两个工作表。该工作簿包含第三个工作表，它只是指向SAS导出工作表中单元格的公式。每当我使用新值重新运行程序时，表3中的公式都不会更新其值。

一个例子是这样的：sheet1 a1 = 2，sheet2 a1 = 3，sheet3 a1 = sheet1！$ a $ 1 + sheet2！$ a $ 1（显示5）
我重新运行SAS导出将前两个值更改为3和4，但sheet3仍然显示5而不是7。

当我点击sheet3单元格评估公式，前两个值显示正确为3和4. 3 + 4的第三个评估显示为5。如果我将公式复制/粘贴到不同的单元格中，则会进行正确的计算，并显示7。如果我将公式剪切/粘贴到另一个单元格，则错误的值显示，但是如果我从那里复制/粘贴到原始单元格，则正确的值显示。

我有Excel 2013，自动计算集，SAS 9.3全部运行在Windows 7和所有64位。

解决方案

使用ODS可以创建Excel公式：

  ODS TAGSETS.EXCELXP 
 file =c：\temp\shoes.xml
 STYLE = minimal 
选项（Orientation ='landscape'
 FitToPage ='yes'
 Pages_FitWidth ='1'
 Pages_FitHeight ='100'）; 
 
 proc print noobs data = sashelp.shoes; 
运行; 
 
 ods tagets.excelxp close; 
  

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.

解决方案

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;

这篇关于sas导出后excel公式不更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！