PROC IMPORT reading old data from Excel Sheet - Even though formula already updated the values

[AngSAS]

Hi SAS Users,

I have come across an issue where, if I read (proc import) a sheet from excel file it is reading old values from columns which contains formula - even though the values have been updated because of the source cells being updated with new values.

For example:

In a sheet named 'TestSheet' in file 'testsheet.xlsx' I have following values.

DataHere	FormulaHere
10	11
9	10

Where first column is in a range called 'DataRange' and the second column is in a range called 'FormulaRange'.

First column has numerical values (in this case 10 and 9) and second column has formula that adds 1 to value in corresponding A column i.e. formula in B2 is A2+1 and in B3 is B2+1, resulting in values 11 and 10 respectively.

If I run proc import now:

proc import 

datafile= "\\<filepath>\testsheet.xlsx"
out= TestDataSet
dbms=xlsx
replace;
sheet="TestSheet";
GETNAMES = YES;
run;

I get values as expected.

DataHere  FormulaHere

10             11

  9             10

Now, if I run following script to update the values in column A to 1 and 2 using SAS dataset 'testdata':

data testdata;
input DataHere;
datalines;
1
2
; 

libname xl Excel "\\<filepath>\testsheet.xlsx";

proc sql;
drop table xl.DataRange;
create table xl.DataRange as
select * from testdata;
quit;

libname xl clear;

Then it success fully updates the values in column A; which should have updated the values in column B as well.

However, when I run proc import again to read data from the sheet:

proc import 

datafile= "\\<filepath>\testsheet.xlsx"
out= TestDataSet
dbms=xlsx
replace;
sheet="TestSheet";
GETNAMES = YES;
run;

SAS only gets updated values from column A but not column B (gets old values):

DataHere  FormulaHere

1              11

2              10

Even though when I open the excel spreadsheet I can see the values updated in column B as well: 

DataHere  FormulaHere

1              2

2              3

Is there a way to get SAS to read the updated Data?

[Kurt_Bremser]

The evaluation of formulas is a function of the Excel software. SAS only updates Excel data, but does not emulate the behaviour of the Excel software in any way. To update the results of Excel formulas, you have to load the workbook in Excel and make it refresh the spreadsheets.

Frankly, when you have SAS at hand, why do you still pack logic into Excel? Do your calculations in SAS, and load the end results into Excel; with ODS Excel, you can even do all the pretty formatting.

Avoid multi-platforming of your logic as much as you can.

[AngSAS]

Thank you for your reply.

Since the code was handed over from someone else, we were trying to see if there is a way to make this work without have to transfer all the calculations to SAS that. However, that is what we ended up doing - moving the calculations to SAS.