BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aabbccwyt
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

View solution in original post

6 REPLIES 6
SASKiwi
PROC Star

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
Obsidian | Level 7

Hi. Now it comes with an error

ERROR: File HAVE.'Sheet1$'. DATA does not exist.
Patrick
Opal | Level 21

@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
Super User Tom
Super User

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
Super User
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
Super User
options validvarname=any validmemname=extend;
libname x xlsx 'c:\temp\temp.xlsx';
proc copy in=x out=work;
run;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1147 views
  • 4 likes
  • 6 in conversation