topic Re: Import multiple xlsx files with multiple sheets in SAS Programming

Import multiple xlsx files with multiple sheets

Mxmaverick — Fri, 24 May 2019 15:46:28 GMT

Hi everyone, I just wanted to know if anyone knows how to import many excel files and at the same time, import specific sheets from this files. I already have a macro that can do both process but separately. Thank in advance. BTW this are my two codes

THIS ONE IMPORT MULTIPLE FILES

%macro MultImp(dir=,out=);

%let rc=%str(%'dir %")&dir.%str(\%" /A-D/B/ON%');
filename myfiles pipe %unquote(&rc);

data list;
length fname $256.;
infile myfiles truncover;
input myfiles $100.;

fname=quote(upcase(cats("&dir",'\',myfiles)));
out="&out";
drop myfiles;
call execute('
proc import dbms=xlsx out= _test
datafile= '||fname||' replace ;
run;
proc append data=_test base='||out||' force; run;
proc delete data=_test; run;
');
run;
filename myfiles clear;

%mend;

proc import dbms=xlsx out= merged
datafile= "C:\USERS\.....\.XLSX" replace ;
run;

%MultImp(dir=C:\Users\Usuario\Desktop\Prompts\Para_Promp_1\Originales\Empresas_Certificadas_V2\Empresas_Certificadas_Despacho_de_Mercancías_98LA,out=merged);

and THIS ONE IMPORT MULTIPLE SHEETS

%macro dataload(sheet);

proc import out= &sheet
datafile = 'C:\Users\...\.....xlsx'
dbms = xlsx replace;
sheet = "&sheet";
getnames = yes;
run;

%mend dataload;
%dataload(sheet1);
%dataload(sheet2);

Re: Import multiple xlsx files with multiple sheets

Reeza — Fri, 24 May 2019 15:55:35 GMT

%macro importXLSX(path=);

libname myfile XLSX "&path";

proc copy in=myfile out=work;
run;

libname myfile;

%mend;

%let rc=%str(%'dir %")&dir.%str(\%" /A-D/B/ON%');
filename myfiles pipe %unquote(&rc);

data list;
length fname $256.;
infile myfiles truncover;
input myfiles $100.;

fname=quote(upcase(cats("&dir",'\',myfiles)));
out="&out";
drop myfiles;

str = catt('%importXLSX(path=', fname, ');');

call execute (str);

run;

Your macro imports and appends the file to a single format. Are you expecting that as well. Otherwise, something like the above may work.

1. Make a macro to import all files from one XLSX doc using LIBNAME

2. Call macro for each XLSX file.

@Mxmaverick wrote:

Hi everyone, I just wanted to know if anyone knows how to import many excel files and at the same time, import specific sheets from this files. I already have a macro that can do both process but separately. Thank in advance. BTW this are my two codes

THIS ONE IMPORT MULTIPLE FILES

%macro MultImp(dir=,out=);

%let rc=%str(%'dir %")&dir.%str(\%" /A-D/B/ON%');
filename myfiles pipe %unquote(&rc);

data list;
length fname $256.;
infile myfiles truncover;
input myfiles $100.;

fname=quote(upcase(cats("&dir",'\',myfiles)));
out="&out";
drop myfiles;
call execute('
proc import dbms=xlsx out= _test
datafile= '||fname||' replace ;
run;
proc append data=_test base='||out||' force; run;
proc delete data=_test; run;
');
run;
filename myfiles clear;

%mend;

proc import dbms=xlsx out= merged
datafile= "C:\USERS\.....\.XLSX" replace ;
run;

%MultImp(dir=C:\Users\Usuario\Desktop\Prompts\Para_Promp_1\Originales\Empresas_Certificadas_V2\Empresas_Certificadas_Despacho_de_Mercancías_98LA,out=merged);

and THIS ONE IMPORT MULTIPLE SHEETS

%macro dataload(sheet);

proc import out= &sheet
datafile = 'C:\Users\...\.....xlsx'
dbms = xlsx replace;
sheet = "&sheet";
getnames = yes;
run;

%mend dataload;
%dataload(sheet1);
%dataload(sheet2);

Re: Import multiple xlsx files with multiple sheets

Mxmaverick — Fri, 24 May 2019 16:57:04 GMT

What I want is to import all files in a directory and at the same time, import specific sheets from those files

Re: Import multiple xlsx files with multiple sheets

Reeza — Fri, 24 May 2019 16:59:21 GMT

Modify the PROC COPY to select only the sheets of interest, using the SELECT statement.
proc copy in=mylib out=work;
select sheet1 sheet2;
run;