topic Losing named ranges when reading COPY of excel file in SAS Programming

Losing named ranges when reading COPY of excel file

evp000 — Mon, 07 Nov 2016 15:25:20 GMT

Hi all,

Here's a weird situation. I have an excel file that has named ranges. When I read the original using...

libname spec excel "<original location>\&currentspec" mixed = yes access=readonly;

...I can see the named ranges in the SPEC libname.

But when I copy the file with...

%sysexec copy "<original location>\&currentspec" "<new location>\&currentspec";

...I can see the ranges when I open the copy in Excel but can't see them when I read the file with the same libname statement as above.

libname spec excel "<new location>\&currentspec" mixed = yes access=readonly;

Thoughts?

Re: Losing named ranges when reading COPY of excel file

RW9 — Mon, 07 Nov 2016 15:37:18 GMT

What its likely to be (and I cant check), is that when you define ranges in Excel, it puts extra information in, for instance a range could be defined as:

Range("A1:A2")

Or more accurately:

Range("Sheet1!A1:A2")

Or more accurately:

Range("MyExcelFile$Sheet1!A1:A2")

etc.

What i suspect is happening behind the scenes is that the range has this file information on it, and when you make a copy - the filename would not match, hence the range is no longer found. Tray copying the file to another path, and make sure the filename matches exactly, that may fix it. Personally however, as always, I would just dump the data to CSV. Any reason why you need to copy the file in the first place?

Re: Losing named ranges when reading COPY of excel file

evp000 — Mon, 07 Nov 2016 17:26:44 GMT

Hi,

Thanks for your input. The name of the file hasn't changed. It's exactly the same. And the copy has the ranges in it. I can see them when I open the file in Excel. I just don't have them in SAS. I tried copying manually and I have the same problem.

Re: Losing named ranges when reading COPY of excel file

Tom — Mon, 07 Nov 2016 17:38:59 GMT

Can you just use the XLSX engine instead of the EXCEL engine? Does that see the named ranges? Perhaps taking actual EXCEL out of the process will make it work better.

Not sure how you would confirm it, but I suspect it is releated to how Office stores both formulas and the current value of the formula. So it might store the range definition in symbolic form and also as the resulting detailed definition form. And SAS might be getting the second version that is trying to refer back to the original file location.

Another thing to watch out for is that Excel does not like to open two files with the same name (even when they come from different directory paths). Perhaps your instance of Excel is getting confused by this.

Re: Losing named ranges when reading COPY of excel file

evp000 — Mon, 07 Nov 2016 17:44:22 GMT

Thanks, I'll try xlsx when I get a chance. Under normal circumstances, I never touch the original, so I don't open both of them.

Re: Losing named ranges when reading COPY of excel file

evp000 — Mon, 07 Nov 2016 19:37:23 GMT

I don't have 9.4 yet so no XLSX libname engine. 😞

Re: Losing named ranges when reading COPY of excel file

evp000 — Mon, 07 Nov 2016 21:30:12 GMT

The whole point of this named range business was to read all the xlsx spreadsheets using the 2nd line for var names rather than the first. I'm starting to think that the whole process is way too cumbersome, and rather than having the data managers run a macro that defines the named ranges every time they edit the file, it's a lot easier to just read from A2 to Z5000 (or some arbitrarily large number), since SAS only reads the rows and columns that are populated anyway.

So all this becomes a moot point. Thanks for the input though.

Re: Losing named ranges when reading COPY of excel file

RW9 — Tue, 08 Nov 2016 09:33:55 GMT

Well as I noted above, if the filename hasn't changed then you must have moved that file to another location. You cannot have two files with exactly the same path/filename - hence some of the information on that has changed. This is likely why.

Simply put Excel is possibly the worst medium you can use for data transfer, I cannot stress to you how bad it is. Use any proper data transfer format and your life will be so much easier - CSV, XML, dataset, delimted etc. I know, your response is going to be, but we "have" to use Excel, generally I hate that word have to. Even then however you could save the data to Excel - write a macro to do it if you have to, just get away from the horribly unstructured/uncontrolled environment.