How Can I Load Excel Sheet Using LIBNAME XLSX

[KentaMURANAKA]

Hi, there:

I am trying to load dataset from excel file, by using LIBNAME XLSX.

This excel file "TEST.xlsx" has some sheets, which are named "table 1.1-1", "table11_1", etc.

My code is below.

%let test=C:\Users\XXXXX\Desktop\XXXXXXXXXX\test.xlsx;
%put &test.;

libname test xlsx "&test" access=readonly;

data test;
    set test.table11_1;
run;

data test_;
    set test.table 1.1-1;
run;

data test_;
    set test.'table 1.1-1$'n;
run;

In library "test", there are something like SAS datasets, "TABLE 1.1-1" & "TABLE11_1".

In library "work", I can find SAS dataset "test" with no errors & warnings.

But I cannot find "test_" from spreadsheet "table 1.1-1" and SAS says "Syntax error, expecting one of the following ~" or "Couldn't find range or sheet in spreadsheet".

I know SAS usually does not allow hypen or blank as data names.

I would like to use data in sheet "table 1.1-1" using LIBNAME XLSX.

Any advice is welcome.

Thank you in advance.

[Kurt_Bremser · Accepted solution]

Try this

set test.'table 1.1-1'n;

Alternatively, run a proc datasets on library test and see how SAS displays the dataset names.

[KentaMURANAKA]

Hi, Kurt-san:

Thank you for your advice.

Now, I can load "table 1.1-1" data.

Thank you!!

[ballardw]

Please do not make us guess where an error occurs.

When you get an error copy from the log the entire data step or procedure that generates the error along with the text of any notes, messages and the errors. Then paste all that copied text into a code box opened on the forum using the </> icon. The code box is important to preserve formatting of the text and any diagnostic characters that SAS often provides as the main message window will reformat text making those aids less useful.

You may also need to have the option VALIDMEMNAME=extend set to deal with non-standard characters for SAS datasets in the sheet names.