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

[somebody]

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?

[SASKiwi]

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.

[Kurt_Bremser]

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.

[somebody]

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.

[Kurt_Bremser]

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.