proc import sheet=

[yuhuanp]

i have 50 xlsx excel workbooks, in a folder, i want to import the 2nd sheet of each excel and convert to sas datasets.

each of excel has different sheet name, but i know for each excel, i only need the 2nd sheet, is there a way to use sheet= "   "   to accomplish this?

thanks,

Ann

[Seadrago]

yes the sheet="" option in proc import is meant to bring in a specific sheet for excel.

I think a macro would be your best bet if you have 50 workbooks.

I am assuming you are using xlsx format in the below example. I am also assuming the the name of your second sheet in your excel workbook is "Sheet 2". It could have a different name if you named it differently but the default is "Sheet 1", "Sheet 2", etc.

%maco get_ds(xlin=,dsout=);

proc import datafile="&xlin."

out=&dsout.

dbms=xlsx 

replace;

sheet="Sheet 2"

run;

%mend get_ds;

%get_ds(xlin=excel_workbook1.xlsx, dsout=outdata1);

%get_ds(xlin=excel_workbook2.xlsx, dsout=outdata2);

etc.

[yuhuanp]

all 50 excel workbook the sheet2 have different names, the sheet name is not sheet2, it named by products, but i need to read the 2nd sheet from all 50 excels, 

[SASKiwi]

This @Ksharp solution will probably help: https://communities.sas.com/t5/SAS-Programming/import-multi-tab-excel-file-amp-export-with-tabs-in-s...

[AhmedAl_Attar · Accepted solution]

Hi Ann,

Here is a potential solution 

%let myPath=<Path>;  *<--- Change this to point to your directory;
options validvarname=any;

%macro import_sheet2(p_xlsxWorkbook=, p_outDsName=);
	libname xl XLSX "&p_xlsxWorkbook";
	/* discover member (DATA) names */
	ods output members=work.members;
	proc datasets lib=xl;
	quit;
	
	/* Get the second sheet name */
	data _null_;
		set work.members(where=(num=2));
		call symput('l_sheetName',strip(name));
	run;
	/* %put &=l_sheetName; */

	/* Generate the output data set from the second sheet */
	data &p_outDsName;
		SET xl.&l_sheetName;
	run;
	
	/* Clean-up */
	proc delete data=work.members; run;
	libname xl CLEAR;
%mend import_sheet2;

/* Find all xlsx workbooks in specified directory */
data _NULL_;
	length fref $8 filename $100 macroStmt $200;
	rc=filename(fref, "&myPath");
	if rc=0 then
	do;
		did=dopen(fref);
		rc=filename(fref);
	end;
	else
	do;
		length msg $200.;
		msg=sysmsg();
		put msg=;
		did=.;
	end;

	if did <=0 then putlog 'ERR' 'OR: Unable to open directory.';
	dnum=dnum(did);
	do i=1 to dnum;
		filename=dread(did, i);
		/* If this entry is a file, then process */
		fid=mopen(did, filename);
		if fid > 0 then
		do;
			/* Look for xlsx workbooks */
			if (scan(filename,2,'.') = 'xlsx') then
			do;
				macroStmt= '%import_sheet2(p_xlsxWorkbook=%str(&myPath/'||strip(filename)||'), p_outDsName='||scan(strip(filename),1,'.')||');';
				call execute (macroStmt);
			end;
		end;
	end;
	rc=dclose(did);
run;

I didn't use Proc Import, instead I used the XLSX Library Engine that was added in SAS 9.4 M2

Hope this helps

[yuhuanp]

thank you, the code works. 

Ann