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.
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 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:
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.
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;
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;
Thankyou very much, it works perfectly
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.