Import many excel worksheets into single dataset

[phdibart]

I have an Excel workbook with 117 worksheets for each quarter.  I arbitrarily named the A1-A117.  All the worksheets have the same column/variable names.  I would like to import all of these and stack them on top of each other.  Each worksheet has about 5,000 rows with about 150 variables, so trying to do this in Excel would be painful.

I've tried the following, understanding that I'd have to stack them in SAS after they're imported:

libname inex excel 'D:\myfile.xlsx';
proc copy in = inex out = work;
run;

But it produces the following error for each worksheet:

NOTE: Copying INEX.A1$ to WORK.A1$ (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines.
      System Option for BUFSIZE was used.
ERROR: The value A1$ is not a valid SAS name.
ERROR: File WORK.'A1$'n.DATA has not been saved because copy could not be completed.

I'm assuming from the error message that the way the worksheet names are being brought into SAS is with a $ at the end of each name, but I cannot figure out (1) if that's the issue and (2) how to deal with it.  If anyone can help me fix the error I'm getting, and even better, how to import and stack them in one step, I would be greatly appreciative!

[Tom · Accepted solution]

Seems like a strange error. Is it possible there are named ranges defined in the XLSX file?

Did it actually copy any of the sheets into datasets?

Why not just use the XLSX engine instead of the EXCEL engine?

Also show version of SAS are you using.  Either look at the top of your SAS log or show the value of the SYSVLONG macro variable. Example:

%put &=sysvlong;
SYSVLONG=9.04.01M5P091317

[Phil_NZ]

Hi @phdibart 

If I were you, I would import them separately by using a macro, and then merge them together by using PROC SORT combining with data step MERGE (with the option mergenoby=error).

[ballardw]

The last time I had a project like this my code would have looked like:

options validvarname=V7 validmemname=extend;

libname inex xlsx 'D:\myfile.xlsx';
proc copy in = inex out = work noclone;
run;

The options control created variable names with V7 creating standard SAS names instead of name literals and the validmemname=extend just in case one or more of those tabs have an inconvenient space or odd character. It will set SAS to allow non-standard data set names which may be created by the source.

Often the engine used for libname may allow non-standard names which are referred to as name literals and have than "A1$"n appearance. The $ is a frequent character inserted from spreadsheets, too esoteric for me to know why. The XLSX engine may fix that if your names are truly A1. I have also had some experience where I would get the equivalent of an A1 and an "A1$"n resulting sets even though there was only one tab. I just ignored the ones I didn't need and renamed the rest.

My project used the NOCLONE option on Proc Copy because the individual sheets had significantly different lengths of values involved and without it had some columns truncated. Yours may not need that.

I strongly recommend examining the lengths and types of common variables across the data  sets early on. You may have to address compatibility issues working with that many sheets.

[phdibart]

Thank you all for your replies.  It turns out using the XLSX engine as Tom suggested solved the issue.  I included the additional options suggested by ballardw, but didn't notice any different behavior from excluding them.