topic Re: What is the best way to import XLSX files with many sheets? in SAS Programming

What is the best way to import XLSX files with many sheets?

somebody — Sun, 17 May 2020 03:25:55 GMT

I have two thousands of XLSX files. Each file has about 60 sheets. Each sheet contains some data as in the attached photo. What would be the best way to bulk import these files?

Re: What is the best way to import XLSX files with many sheets?

SASKiwi — Sun, 17 May 2020 04:11:35 GMT

One of the best ways I can think of is to use a LIBNAME statement using the EXCEL engine to treat an Excel workbook like a SAS library. Then you can use PROC DATASETS to copy all sheets in a workbook in one statement:

libname MyExcel EXCEL "MyExcelWorkbook.xlsx";

proc datasets library = WORK nolist;
  copy in = MyExcel out = WORK;
run;
quit;

Once you have got this working for one workbook, then you can consider automating this for say all workbooks in a directory. Eventually a SAS macro could be built to do this.

Re: What is the best way to import XLSX files with many sheets?

Kurt_Bremser — Sun, 17 May 2020 06:57:53 GMT

Do all these sheets share the same structure? Anyway, since you will not want to deal with 120000 datasets, you will need the mechanism to combine all these datasets into one or a few. Given that you import directly from Excel, this will be the challenge.

Since I also see that you have data (dates) in structure (column names), a transpose as part of the import process is also in order.

Re: What is the best way to import XLSX files with many sheets?

somebody — Mon, 18 May 2020 01:04:13 GMT

yes, all the sheets are in the same format: the first two columns contains stock identity, the third is type and then other columns would be dates.

Re: What is the best way to import XLSX files with many sheets?

Kurt_Bremser — Tue, 26 May 2020 10:33:33 GMT

I would start out with a list of Excel file names in a data set.

Next, create a macro that

assigns a libname to an Excel file
combines all datasets (sheets) to a single WORK dataset
transposes this dataset to long format, BY the first three columns, deleting observations with missing values
names the log dataset along the name of the excel file

Run this macro repeatedly from the list of Excel file names with CALL EXECUTE.

Finally, combine the resulting long datasets into one, with a SET statement reading all long datasets.