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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.