topic Re: Reading all/multiple excel files in Library in SAS Programming

Reading all/multiple excel files in Library

evach — Fri, 23 Apr 2021 14:13:19 GMT

Hi there,

I have recently started learning SAS programming and question is related to accessing excel files.

I am using SAS Studio.

In lesson 2 of SAS Programming 1, demonstration on using library to read SAS data table is provided.

As per demonstration with following libname, i was able to read/access all SAS Data tables/ data sets into PG1 Library.

libname pg1 "~/EPG1V2/data"; or

libname pg1 "/home/u48512862/EPG1V2/data";

Similarly, demonstration on using library to excel files is provided in lesson 2. However, it is limited to reading single excel work book; named as Class and following is libname statement.

libname xlclass xlsx "~/EPG1V2/data/class.xlsx";

Data sub folder under EPG1V2 also contains other excel file such as, eu_sport_trade.xlsx, np_info.xlsx and storm.xlsx.

Is there a way by which, one can read all excel files at once/ using single libname statement. (similar to that of all SAS data tables were read using libname pg1 "~/EPG1V2/data"; i remember here base is default engine is used for reading SAS Data tables).

Can anyone help with code which will read all excel files from data sub folder into library.

Thanks !!

Re: Reading all/multiple excel files in Library

PhilC — Fri, 23 Apr 2021 15:00:45 GMT

Wow.

libname xlclass xlsx ("C:\Users\SaxophoneChihuahua\styles_vaLight.xlsx"
                      "C:\Users\SaxophoneChihuahua\styles.xlsx");

I ran this and it worked. I've learned something, i didn't expect it would work.

Re: Reading all/multiple excel files in Library

PhilC — Fri, 23 Apr 2021 15:20:45 GMT

But it doesn't WORK! What I should have said was that it doesn't produce an error!

libname xlclass xlsx ("C:\Users\SaxophoneChihuahua\styles_vaLight.xlsx"
                      "C:\Users\SaxophoneChihuahua\styles.xlsx");

... Each file had one spreadsheet and I could only access one of the one spreadsheet in the first file. SAS showed me both spreadsheets, as if it might have worked. bummer

Re: Reading all/multiple excel files in Library

PhilC — Fri, 23 Apr 2021 15:17:47 GMT

I've done this, in a way. I opened several Excel files in a SAS system at one time, but each file had its own LIBNAME. It involved writing SAS code that wrote SAS code for each LIBNAME statement. I'm sure this is something I'm sure you will be capable of if you need to be. Good luck. I hope you find a satisfying solution to this.

Re: Reading all/multiple excel files in Library

Reeza — Fri, 23 Apr 2021 15:30:43 GMT

Unfortunately not as simply as you'd likely like, but it's definitely possible.

This has a bug somewhere, IIRC, but I think it can get you started. If you find the bug, post back and I'll fix the main program.
https://github.com/statgeek/SAS-Tutorials/blob/master/Import_all_files_one_type

Re: Reading all/multiple excel files in Library

ballardw — Fri, 23 Apr 2021 15:39:37 GMT

@PhilC wrote:

But it doesn't WORK! What I should have said was that it doesn't produce an error!
libname xlclass xlsx ("C:\Users\SaxophoneChihuahua\styles_vaLight.xlsx"
                      "C:\Users\SaxophoneChihuahua\styles.xlsx");
... Each file had one spreadsheet and I could only access one of the one spreadsheet in the first file. SAS showed me both spreadsheets, as if it might have worked. bummer

Did your example involve XLSX files with common sheet names? Since SAS uses the sheet name to build a data set name to reference in the library I would expect problems with common names.

One of the reasons I expected this to be a poor idea was how many XLSX files have "sheet1" "sheet2". So whey you try use multiple XLSX files which "sheet1" would be available n the resulting library. I don't think we should expect the libname engine to provide a rename that would differentiate between the source file for the sheet name.

Re: Reading all/multiple excel files in Library

PhilC — Fri, 23 Apr 2021 17:27:55 GMT

They had different names. Does this work for you!? I dont think it works. i could not open the other sheet, yet it appeared in the library, in my Enterprise Guide's GUI anyway. I did only try it once.

Re: Reading all/multiple excel files in Library

Reeza — Fri, 23 Apr 2021 18:46:43 GMT

I find XLSX a little buggy with showing the names of the sheets. Try actually referencing them if you know they exist and see what happens. Not ideal by any means.

Re: Reading all/multiple excel files in Library

ballardw — Fri, 23 Apr 2021 21:29:16 GMT

@Reeza wrote:
I find XLSX a little buggy with showing the names of the sheets. Try actually referencing them if you know they exist and see what happens. Not ideal by any means.

I think you could end that comment at "buggy". 😉

I kind of resort to any of the XLSX code like LIbname or import as a last resort. One of the files I did use it on (because I had many to deal with each having 50 or so tabs to extract small bits of data) kept showing "names" that were not even sheets in the data.

Re: Reading all/multiple excel files in Library

Reeza — Fri, 23 Apr 2021 21:31:25 GMT

Yeah, Excel defined Tables or Named Ranges will also appear. Sometimes print ranges too...
Excel is the most used tool to do analysis though 😞