topic Re: Using libname to read excel into SAS in SAS Programming

Using libname to read excel into SAS

aabbccwyt — Mon, 18 Oct 2021 18:59:02 GMT

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!

Re: Using libname to read excel into SAS

SASKiwi — Mon, 18 Oct 2021 19:03:41 GMT

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;

Re: Using libname to read excel into SAS

aabbccwyt — Mon, 18 Oct 2021 19:11:24 GMT

Hi. Now it comes with an error

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

Re: Using libname to read excel into SAS

Reeza — Mon, 18 Oct 2021 19:13:13 GMT

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

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

Re: Using libname to read excel into SAS

Patrick — Wed, 20 Oct 2021 02:38:56 GMT

@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.

Re: Using libname to read excel into SAS

Tom — Mon, 18 Oct 2021 22:31:24 GMT

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.

Re: Using libname to read excel into SAS

Ksharp — Tue, 19 Oct 2021 12:27:08 GMT

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