topic Re: Writing to Excel named range - code runs successfully but Excel sheet not updated in SAS Programming

Writing to Excel named range - code runs successfully but Excel sheet not updated

robulon — Wed, 06 May 2020 11:48:45 GMT

Hi,

I've been using the libname Excel method of writing to a named range in an Excel sheet for a few monbths, since a colleague at work introduced me to the technique and have found it very useful. I am having issues with it at the moment though in as much as for a particular sheet, when I run the code, the log tells me the code has run successfully but the Excel sheet has not been updated.

I have run the code a couple of times today and it has worked (the Excel file shows as having been modified at 11:08 today) but when I have run it again since, it is not updating. I have checked that it was trying to write to the correct location (which I knew it was) by running the code with the Excel file open and the code errors, and I have checked that the named ranges still exist within the file (which they do) so I just don't know what else it could be.

The output datasets have three variables in, a date, a character variable and a number (in percent format) - example attached.

The code I am using is below: -

%let xlpath= ##\Covid\Arrears_Summary_Covid_&covid..xlsx;
libname xl Excel "&xlpath.";

proc datasets lib=xl nolist;
delete &next_value._Rate;
quit;

data xl.&next_value._Rate;
set &next_value._totals_2_covid_&covid.;
run;

libname xl clear;

And the log shows this: -

NOTE: Libref XL was successfully assigned as follows:
Engine: EXCEL
Physical Name: ##\Covid\Arrears_Summary_Covid_1.xlsx

NOTE: Deleting XL.Improve_Rate (memtype=DATA).
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.00 seconds
11 The SAS System 10:39 Wednesday, May 6, 2020

cpu time 0.00 seconds

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: There were 52 observations read from the data set WORK.IMPROVE_TOTALS_2_COVID_1.
NOTE: The data set XL.Improve_Rate has 52 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: Libref XL has been deassigned.

Does anyone have any thoughts as to what could be causing this, only it's a huge frustration for me at the moment. Nothing of any consequence has been changed in the code between runnings so I'm really at a loss as to why it worked previously but not now.

Thanks,

Rob

Re: Writing to Excel named range - code runs successfully but Excel sheet not updated

Reeza — Wed, 06 May 2020 15:19:34 GMT

What happens if you just run the delete portion?

Please post the log as well.

Re: Writing to Excel named range - code runs successfully but Excel sheet not updated

ballardw — Wed, 06 May 2020 15:22:36 GMT

Where are you indicating the named ranges?

Re: Writing to Excel named range - code runs successfully but Excel sheet not updated

Reeza — Wed, 06 May 2020 15:40:03 GMT

There's one issue I recall running into when using this methodology - if your data is larger than the named range it won't export it, nothing happens. Your named range must be larger than the data set space needed, ie your number of rows and columns needs to be number of observations +1 and number of variables, respectively. If you test it step by step as I indicated in my last post you'll be able to see if the program is running at all or if this is the issue pretty quickly.

Re: Writing to Excel named range - code runs successfully but Excel sheet not updated

robulon — Thu, 07 May 2020 05:39:07 GMT

Thanks Reeza, I'll have a look into this. I'm not sure that what you're saying about the size of the data is quit correct for a couple of reasons. Firstly, I use this approach in a number of other programs which add a new observation each day so the size of the data set increases by one each time and they all work. The other reason being that my understanding is that by deleting the range using proc datasets, the initial size of it shouldn't come into play. I will have a look at running the program in its individual parts and see if that tells me anything - thanks.

Re: Writing to Excel named range - code runs successfully but Excel sheet not updated

robulon — Thu, 07 May 2020 05:41:05 GMT

Hi, the range is &next_value._Rate, so is deleted in proc datasets, before being written afresh in the data step.

Re: Writing to Excel named range - code runs successfully but Excel sheet not updated

Reeza — Thu, 07 May 2020 15:48:26 GMT

@robulon wrote:
by deleting the range using proc datasets, the initial size of it shouldn't come into play.

You do not delete the range, it deletes the data in the range but the named range still exists. You can check this when you run your code in sections.