BookmarkSubscribeRSS Feed
Emma2021
Quartz | Level 8
I have multiple excel (xls or XLSX) files. I would like to import into sas but the sheet names are “upcoming” or sometimes “upcomings” -added s or not. How can I read into without checking the exact sheet name? Such is SHEET=upcoming* would not work.
Thank you.
4 REPLIES 4
whymath
Lapis Lazuli | Level 10
If there is only one sheet in each excel file, you can using proc import without "sheet=" option.
Emma2021
Quartz | Level 8
Each excel file has multiple sheets but I wanted only start with upcoming*

Thank you.
Tom
Super User Tom
Super User

For XLSX files you can just read the actual XML file that is inside the file to find the sheet names.

For example to find the sheets in the xlsx file named book1.xlsx in the directory c:\downloads I could run this program.

data worksheets;
  infile "c:\downloads\book1.xlsx" zip member='xl/workbook.xml' recfm=n dsd dlm=' ';
  sheet_number+1;
  input @'<sheet name=' sheet_name :$32. @@;
run;

No idea how you would do it with XLS files (why do you still have XLS files anyway?).

A_Kh
Lapis Lazuli | Level 10

If you have SAS/ACCESS then  Libname xlsx engine will allow to read all sheets without checking sheet names. Refer to the documentation https://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/ 

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
  • 4 replies
  • 509 views
  • 4 likes
  • 4 in conversation