Importing Excel file with libname, get message "Multiple SAS files with this name exist"

[mdwilson]

I've been importing Excel files using the libname statement for years with no issue. Suddenly today, the resulting file is empty and I get a pop up window that says "Multiple SAS files with this name exist" and then offers to open one of them. This file is then empty. I've gone back to several code files that all worked fine just a week ago and none of them work today. Here's the code:

libname HEI78 xlsx "H:\MyFolder\NHANES\HEI2010_NHANES0708.xlsx";
proc datasets lib=HEI78 details; run; quit;

Here's the log:

1
2 libname HEI78 xlsx "H:\MyFolder\NHANES\HEI2010_NHANES0708.xlsx";
NOTE: Libref HEI78 was successfully assigned as follows:
Engine: XLSX
Physical Name: H:\MyFolder\NHANES\HEI2010_NHANES0708.xlsx
3
4 proc datasets lib=HEI78 details;
NOTE: Writing HTML Body file: sashtml.htm
5 run;

6 quit;

NOTE: PROCEDURE DATASETS used (Total process time):
real time 9.56 seconds
cpu time 1.15 seconds

Any help would be appreciated. I'm pretty much just sitting here whimpering. 

[ballardw · Accepted solution]

Are any of the TABS in the spreadsheet named the same IF spaces or special characters were replaced with underscore? Such as "Tab&1" and "Tab 1"? The import might be replacing the & and space with _ generating duplicates.

Or run proc copy to make copies of the data set in another library and see what the log shows.

Something like
Proc copy in=HEI78 out=work noclone;

run;

If I were using data from NHANES I would most likely use the XPT transport files if downloading from CDC.GOV if at all possible, partially just to avoid all of the problems XLSX files seem to introduce into every project.

[mdwilson]

Thanks so much! Looks like just getting them into the work folder, even using a data step was all it needed. I hadn't used the code in a while and got an error message when I first ran it and I think trying to open the file in the library just dropped me down the rabbit hole.

I saved the files as Excel after a god-awful data wrangle so they'd be there as I need them without having to go through it all again. To tell you the truth, importing .XPT files has never worked for me. I end up having to just open them by double clicking on them. The code I've found on this site always gives me errors and I give up and just start double clicking.