I think I may have a rather unique issue and have not yet found a viable solution. I am using this macro to scan a folder for xlsx files, create a list of files in the folder (all of identical format), and then import them. I am interested in importing 6 sheets from each file so I have adjusted the code accordingly. The reason I mention this is because I will be importing thousands of files, each exported manually from on online database and saved to said folder. The issue I am running into using a simple proc import step with sheet statements (tested outside of macro) is "ERROR: Couldn't find sheet in spreadsheet." When commenting out the sheet statement and trying to just import the file (defaulting to importing the first sheet) I get "No worksheets in the .XLSX file Requested Input File Is Invalid ERROR: Import unsuccessful. See SAS Log for details." After diligently checking sheet names, file names, etc., I have discovered that the issue lies in one of two problem: 1) these files are not being opened and saved prior to attempting to import them into SAS which may or may not tie into 2) the files are in protected view and have not been opened and saved to enable editing. Does anyone have any ideas how I could get around this without opening and saving every single file? Again, there are thousands.
Have you tried the EXCEL or XLSX LIBNAME engines? They may behave differently to that macro.
I couldn't replicate the issue you describe in my SAS9.4M7 Windows workstation environment.
I've downloaded from the Reserve Bank of Australia Excel https://www.rba.gov.au/statistics/tables/xls/a03-2hist.xlsx?v=2023-10-18-13-54-25
After the download I can execute both of below code versions without any issues.
/* version 1 */
%let workbook=a03-2hist.xlsx;
proc import
file="c:\temp\&workbook" dbms=excel replace out=work.test;
sheet='rba securities lending';
run;
/* version 2 */
%let workbook=a03-2hist.xlsx;
libname test "c:\temp\&workbook" access=readonly;
data _null_;
set test.'rba securities lending$'n;
run;
libname test clear;
Even after execution of above code when I open the downloaded file via Excel I still get first:
Also interesting is that if I download the file via a REST call then I get a file that opens without Protected View.
%let workbook=a03-2hist.xlsx;
filename out "c:\temp\&workbook";
proc http
url="https://www.rba.gov.au/statistics/tables/xls/&workbook?v=2023-10-18-13-54-25"
method="get"
out=out
;
run;
filename out clear;
If things aren't working this way for you then you might need to talk to a Windows admin at your site.
Solution: using Microsoft Power Automate instead to automate opening and saving the downloaded files in the folder. This was the easiest and quickest solution I could piece together to circumvent the issue.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.