Hi, Maybe if you have access to a PIPE mechanism on your computer you could, instead of using macro, try do it in a datastep with call execute, something like that: /* for linux */
%let firstPartOfFilePath=/home/bart/SAS/test_folders; /*a base path for data */
filename f PIPE "find ""&firstPartOfFilePath."" | grep -i xlsx"; /* */
/* for windows */
/*
%let firstPartOfFilePath=E:\SAS_WORK_5400\test_folders;
filename f PIPE "dir ""&firstPartOfFilePath.\*"" /s/b";
*/
data test;
infile f dlm='0a0d'x;
input path : $ 1024.; /* read in data from PIPE line by line */
/* create required variables */
year = scan(path,-3,"/\");
month = scan(path,-2,"/\");
deparmentname = scan(path,-1,"/\");
deparmentname = substrn(deparmentname, 1, length(deparmentname)-5); /* to drop the XLSX extension */
/* use call execute to execute import */
call execute ("PROC IMPORT");
call execute (catx("_", "OUT=", deparmentname, year, month));
call execute (cats('DATAFILE= "', path, '"'));
call execute ("DBMS=xlsx REPLACE; RUN;");
run; all the best Bart
... View more