topic Re: Multiple excel sheets and get all headers from each excel file in SAS Programming

Multiple excel sheets and get all headers from each excel file

Emma2021 — Thu, 27 Apr 2023 02:29:30 GMT

I have multiple excel files with multiple sheets. From each excel file, if sheet name is as “data” then I would like to get all column headings’ names. How can I do that in SAS. Thank you.

Re: Multiple excel sheets and get all headers from each excel file

Reeza — Thu, 27 Apr 2023 16:08:24 GMT

Figure it out for one and then figure out how to scale it.

If you assign a libname to the Excel file and then check the data tab does it have the column headers?

libname myxl 'path to xlsx file';

proc contents data=myxl.data;
run;

Re: Multiple excel sheets and get all headers from each excel file

Emma2021 — Thu, 27 Apr 2023 15:38:04 GMT

Sorry, can you write the complete code? I have to use sheet name as “data” since there can be multiple sheets. Thank you.

Re: Multiple excel sheets and get all headers from each excel file

Tom — Thu, 27 Apr 2023 15:41:26 GMT

You can just use the XLSX engine to treat the workbook as a library of datasets.

The NAME of the variables will be derived from the headers in the first row of each sheet.

libname myfile xlsx 'myfile.xlsx';
proc contents data=myfile._all_ out=contents noprint; run;
proc print data=contents;
  by memname;
  id memname;
  var varnum name label ;
run;

Re: Multiple excel sheets and get all headers from each excel file

AhmedAl_Attar — Thu, 27 Apr 2023 16:51:42 GMT

Hi @Emma2021

I'll use portion of @Tom's code

libname myfile xlsx 'myfile.xlsx';
proc contents data=myfile._all_ out=contents noprint; 
run;
libname myfile clear;

/* Extract the "DATA" sheet column headers */
DATA want(keep=name);
   set contents(keep=name memname where=(memname='DATA'));
run;

Re: Multiple excel sheets and get all headers from each excel file

Emma2021 — Thu, 27 Apr 2023 17:03:48 GMT

The excel files are xls or xlsx and also each excel file has multiple sheets —I only need column names for sheet names “data”. How can I do that?

Re: Multiple excel sheets and get all headers from each excel file

Tom — Thu, 27 Apr 2023 19:43:12 GMT

XLS files are much harder as there is no XLS libname engine to use.

1248  libname xls xls "c:\downloads\test_class.xls" ;
ERROR: The XLS engine cannot be found.
ERROR: Error in the LIBNAME statement.

If you are running on windows (and you can get it to work) you might be able to use the EXCEL libname engine instead.

If you want to only see the names of the variables in the datasets that have the string 'DATA' in their name then just subset the CONTENTS data.

proc print data=contents;
  where memname like '%DATA%' ;
  var memname varnum name;
run;