Here is the whole macro: options validvarname=any; %macro MultImp(dir=,out=); * dir of filenames; %let flnm=%str(%'dir %")&dir.%str(\%" /A-D/B/ON%'); filename myfiles pipe %unquote(&flnm); * list of filenames; data list; length fname $256.; infile myfiles truncover; input myfiles $100.; fname=quote(upcase(cats("&dir",'\',myfiles))); call symput('fname_loc',cats("_","&dir",'\',myfiles)); out="&out"; drop myfiles; * import this range through the list of filenames; call execute(' * import excels; proc import dbms=xlsx out=_test10 datafile= '||fname||' replace ; range="Sheet1$A5:BE2000"; run; * varnames without spaces or special chars; proc sql; select cats("'",cats(name),"'n","=",compress(name,"_","ADK")) as name1 /*%str(cats(%"%'%",cats(name),%"%'n%",%"=%",compress(name,%"_%",%"ADK%"))) as name1*/ into :renamecolumns separated by " " from dictionary.columns where libname="WORK" and memname="_TEST10" ; quit; %put &renamecolumns; /* Rename the variables */ data _TEST20; set _TEST10 (rename=(&renamecolumns)); run; * add original filename; data _test20; set _test20; bestandsnaam="&fname_loc"; run; *add all; data '||out||'; set '||out||' _test; run; proc delete data=_test; run; '); run; filename myfiles clear; %mend; %multimp(dir=d:\excel,out=out_100)
... View more