Using libname to read excel into SAS

[aabbccwyt]

I'm using libname to read in excel into SAS

libname have xlsx '\\client\c$\SAS\File';
data need;
set have. 'Sheet1$';

And I'm receiving the following error

Error: The value 'SHEET1$'n is not a valid SAS name.

Error 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, ...

Error 201-322: The option is not recognized and will be ignored.

Any thoughts? Thank you in advance!

[SASKiwi]

Try this. Using a non-standard SAS name means you have to specify a text string name.

libname have xlsx '\\client\c$\SAS\File.xlsx';
data need;
set have.'Sheet1$'n;

[aabbccwyt]

Hi. Now it comes with an error

ERROR: File HAVE.'Sheet1$'. DATA does not exist.

[Patrick · Accepted solution]

@aabbccwyt And once you're libname is correct - something like...

libname have xlsx '\\client\c$\SAS\myWorkBook.xlsx';

... run a proc contents so you understand what names the tables get within SAS.

proc contents data=have._all_;
run;quit;

Any table or column name that doesn't comply with SAS naming standards must get addressed as SAS name literal within SAS code: <libref>.'table name'n   (=table name in quotes and letter n after closing quote).

If you use options validvarname=v7; before the libname statement then SAS will replace all non compliant characters for column names with an underscore.

[Tom]

If by TABLE NAME you mean the names of the SAS datasets in the library then the option to allow non-standard characters in those name is VALIDMEMNAME.  The VALIDVARNAME option only controls the names allowed for variables (what you are calling COLUMNs). 

Set VALIDMEMNAME=EXTEND to work with sheets in the workbook that have spaces or other non-standard characters in them.  To turn it off set it back to VALIDMEMNAME=COMPAT.

[Reeza]

Your path has to reference the XLSX file directly - including the extension.

And you need to remove the spaces between the have and period.

[Ksharp]

options validvarname=any validmemname=extend;
libname x xlsx 'c:\temp\temp.xlsx';
proc copy in=x out=work;
run;