topic Re: Excel named ranges intermittently not saving in Microsoft Integration with SAS

Excel named ranges intermittently not saving

epp_dog — Mon, 10 Apr 2017 14:45:52 GMT

I have an intermittent issue that is baffling me.

SAS EG Version 7.13 (7.100.3.5408) (64-bit)

Excel 2013

We use a LIBNAME statement to access an existing Excel workbook that is in a production area so team members cannot access it (i.e. no person has the file open during run time).
We run PROC DATASETS to delete the desired excel named ranges.
We then use DATA steps to write to those named ranges.
Finally we clear the libname.

98% of the time everything works just great. But occasionally the program will finish with no issues/warnings/errors noted in the log (actually, the log tells me that the records were written to the named range) but the named ranges have not been updated and the "Date modified" of the Excel file in windows explorer is also from the prior week. Whenever this happens we simply open the workbook, go to a blank cell outside of a named range, enter something, delete what we just entered, resave and close the workbook. Then rerunning the SAS code will update the named ranges.

This seems like this is likely more a windows/excel issue more than SAS but the output is going to a client via an automated job so every few months they receive the prior week's report.

Wondering if anyone else has come across this and what they discovered was the actual root cause.

Thanks

David

Abbreviated log file below:

LIBNAME finance EXCEL "S:\Prod_Reporting\SAS_Projects\output\&OUTFILE..xlsx";
NOTE: Libref FINANCE was successfully assigned as follows:

Engine: EXCEL
Physical Name: S:\Prod_Reporting\SAS_Projects\output\finance_reports_MTD.xlsx

*** clear named ranges; run;
PROC DATASETS LIB=finance;
DELETE
raw_dates;
QUIT;

NOTE: Deleting FINANCE.raw_dates (memtype=DATA).

*** write data to named ranges; run;
*** Date Macros ***;
DATA finance.raw_dates (rename=(period="Report Period"n value ="Date Value"n));
SET raw_dates;
RUN;

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: There were 15 observations read from the data set WORKSAVE.RAW_DATES.
NOTE: The data set FINANCE.raw_dates has 15 observations and 2 variables.

LIBNAME finance CLEAR;
NOTE: Libref FINANCE has been deassigned.

Re: Excel named ranges intermittently not saving

ballardw — Mon, 10 Apr 2017 20:10:10 GMT

When I see something like:

*** Date Macros ***;

That makes me think that there were one or more unspecified macro run to create the data set. Was that the case?

Perhaps the issue is with the raw_dates set not getting replaced from a previous run, or not getting created in time to write out.

If you just rerun the program without the Open and associated Excel manual steps does the program work?

Re: Excel named ranges intermittently not saving

epp_dog — Mon, 10 Apr 2017 20:33:21 GMT

There are actually about 20 named output ranges and I didn't want to bore everyone with all of them but NONE of them got written yesterday night. That one is just where I am writing out a dataset (raw_dates) of all the date macros I used during the running of the program. (So I have a datastep earlier where I write rows to store off each value).

When this happens, if I do not open the spreadsheet and do the "add something/delete/save" and instead just rerun the wrote to Excel portion, it does not update the data.

Re: Excel named ranges intermittently not saving

Patrick — Tue, 11 Apr 2017 12:34:29 GMT

The SAS log is pretty clear. The Excel Sheet has been deleted and has been re-created and it also tells you how many row have been written to it.

And you tell us that you end up not only with the change not applied but even with the Excel workbook not showing the correct modification date!

It's only a theory but that sounds to me very much like some recovery process doing the wrong thing. I'd contact your server admins and ask them if it's possible to get some event log for this Excel workbook.

Besides of getting to the "why" I would assume you want the problem solved asap and have a stable process. I believe it could become a painfull undertaking to get to the bottom of what's going on here so if I were you I'd go for an interim solution where I change my process like:

create a copy of the Excel workbook and apply the changes there, then replace your original with the copy. That's a bit Voodoo I know. It's just trying to do something different in the hope that this will circumvent not yet discovered interfering processes.

You could also add a validation step before you send out the Excel, i.e. check a date column which you know must be close to the current date if it's the Excel is in the right version. ....or eventually create always a new Excel workbook with a date in the name.

Re: Excel named ranges intermittently not saving

epp_dog — Tue, 11 Apr 2017 12:48:16 GMT

Thanks Patrick,

You are right, data isn''t updated and the file "save timestamp" isn't updated either.

I agree, your suggestion is a bit of voodoo but it might just work (and in my 20 years of SAS coding I certainly have come to accept some strange solutions) . I have always assumed it to be more of a windows/excel issue than SAS.

I'll play around with your suggestion of double checking the values in the spreadsheet as that would eliminate the embarassement of sending the client last week's report!

David

Re: Excel named ranges intermittently not saving

Patrick — Tue, 11 Apr 2017 13:15:08 GMT

I'll play around with your suggestion of double checking the values in the spreadsheet

And to add some more Voodoo: I would clear and re-assign the libname before this validation step to have more certainty that you're reading the actual Excel and not some cached version.