topic Re: Multiple sheet names in SAS Programming

Multiple sheet names

Emma2021 — Wed, 26 Apr 2023 01:29:43 GMT

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.

Re: Multiple sheet names

whymath — Wed, 26 Apr 2023 02:26:51 GMT

If there is only one sheet in each excel file, you can using proc import without "sheet=" option.

Re: Multiple sheet names

Emma2021 — Wed, 26 Apr 2023 02:34:02 GMT

Each excel file has multiple sheets but I wanted only start with upcoming*

Thank you.

Re: Multiple sheet names

Tom — Wed, 26 Apr 2023 02:48:52 GMT

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?).

Re: Multiple sheet names

A_Kh — Wed, 26 Apr 2023 14:11:41 GMT

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/