always import second sheet from multiple excel files without using a macro

[Anita_n]

Dear all,

If I have multiple excel files. Each files has two sheets. I wish to import only the second sheet in all files.

I was just wondering if it's possible to do this without using a macro.

[Tom]

---

@Anita_n wrote:

Dear all,

If I have multiple excel files. Each files has two sheets. I wish to import only the second sheet in all files.

I was just wondering if it's possible to do this without using a macro.

---

Yes.

Provide more details for more help.  In particular things like:

• Is the sheetname of the second sheet always the same?
• Do you know the names of the workbooks you want to import?
• What names to you want to use for the imported datasets?

Note that SAS macro language is used to generate SAS code.  There are many other ways to use normal SAS code to generate SAS code that does not require using the macro processor.

[Anita_n]

Yes I know the names of the workbooks. The sheetnames are different in some files but are the same in some

I was thinking if there is a way to say second always import sheet2

proc import datafile=infile&year
out=outfile&year
dbms=xlsx replace; getnames=no; datarow=7;
sheet="sheet2"; *maybe there is a way to say always import sheet2 without using a macro;
run;

[Tom · Accepted solution]

If you don't know the NAME of the sheet you will have to use some method to discover the name.

Here is a way using the ZIP filename engine to treat the XLSX file as the ZIP file that it actually is so that you can read the XML file that is in there that lists the sheets.  The XML is pretty simple so just use a data step to locate the names and don't bother trying to interpret all of the XML.

filename _wbzip ZIP "c:\downloads\contents.xlsx" member='xl/workbook.xml';
data sheets ;
  infile _wbzip dsd dlm=' ' recfm=n ;
  sheetnum+1;
  input @'<sheet name=' sheetname :$32. @@;
run;

Once you have the sheetname you can put it into a macro variable and use that to generate the PROC IMPORT step.

data _null_;
  set sheets;
  if sheetnum=2;
  call symputx('sheet',sheetname);
run;

proc import ....
  sheet="&sheet."; 
run;

[Anita_n]

Thankyou very much, it works perfectly