Xlsx import and sheet name has multiple spaces

[Emma2021]

I am using sas 9.4. I want to import excel file into sas. There are many tabs that has some tabs’ names have extra spaces. How can read those sheets without opening the excel file and remove those spaces?

When I use this below code -I got an error message (file _imex_ data does not exist).

My code is below:

Proc import out= dsn
Datafile =“location\in.xlsx”
DBMS=excel replace ;
Sheet=“sheet1 control$”n;
Run;

Thank you.

[Tom]

What exactly is your issue?  Are you not finding the XLSX file?  What does the FILEEXIST() function return for that path?

Is your SHEET statement not finding the sheet name?  The value there needs to be the specification of the name of the sheet in the XLSX file, not the SAS name literal string you have tried.  Try adding quotes inside the quotes.

Sheet="'sheet1 control$'" ;

Or switch to using the XLSX libname engine instead.

To allow member names to contain spaces you can change the VALIDMEMNAME option.

options validmemname=extend;
/* Do not use Microsoft ugly quotes like, “...”, use normal quotes */
libname in xlsx "location\in.xlsx";  
proc copy inlib=in outlib=work;
run;

To reference the member name with the spaces you will need to use a name literal.  I recommend renaming the member.

proc datasets nolist lib=work;
  change "sheet1 control"n = sheet1_control ;
  run;
quit;

[ballardw]

---

@Emma2021 wrote:
I am using sas 9.4. I want to import excel file into sas. There are many tabs that has some tabs’ names have extra spaces. How can read those sheets without opening the excel file and remove those spaces?

When I use this below code -I got an error message (file _imex_ data does not exist).

My code is below:

Proc import out= dsn
Datafile =“location\in.xlsx”
DBMS=excel replace ;
Sheet=“sheet1 control$”n;
Run;

Thank you.

---

Large economy sized warning: This forum's message windows will reformat text pasted and the most common reformat is removing multiple spaces. It is best to always paste code into either a code box opened with the "running man" icon or a text box opened with the </> icon above the message windows to preserve formatting.