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);
%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);
What I want is to import all files in a directory and at the same time, import specific sheets from those files
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.