Multiple excel sheets and get all headers from each excel file

[Emma2021]

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.

[Reeza]

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;

[Emma2021]

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

[AhmedAl_Attar]

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;

[Tom]

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;

[Emma2021]

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?

[Tom · Accepted solution]

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;