I've updated the code and altough it partly does what i want, it does not move the error files into the correct folder. Any help would be appriciated. *+------------------------------------------+
| SET DATES
+-------------------------------------------+;
data _null_;
today=put(today(),yymmddn8.);
call symput('TD',put(today(),date9.));
run;
*+------------------------------------------+
| SET DIRECTORY PATH
+-------------------------------------------+;
%LET PATH=C:\Users\Admin\Desktop\Folder\;
*+------------------------------------------+
| CREATE DATED FOLDERS
+-------------------------------------------+;
Filename Create PIPE
"mkdir &path.\&TD";
data _null_ ;
infile Create;
input ;
put _infile_;
run;
Filename Create PIPE
"mkdir &path.\ERROR_&TD";
data _null_ ;
infile Create;
input ;
put _infile_;
run;
*+------------------------------------------+
| LIST ALL FILES IN THE DIRECTORY
+-------------------------------------------+;
%let dir=&PATH\*.xls;
filename dirlist pipe "dir &dir /b";
data filenames;
infile dirlist truncover dlm='.' end=end;
input member :$200.;
call symput('file'||trim(left(_N_)),trim(left(member)));
call symput('nobs',trim(left(_N_)));
run;
proc print data=filenames;
run;
*+----------------------------------------------------------------------------------------------------------+
| IF EXCEL FILE EXTENSION IS .XLSM OR .XLSX MOVE TO DATED ERROR FOLDER AS THIS IS NOT THE APPROVED TEMPLATE
+-----------------------------------------------------------------------------------------------------------+;
%macro extensioncheck;
%do i=1 %to &nobs;
data _null_;
RC3=rename("&path.\&&file&i...xlsm",
"&path.\ERROR_&TD\&&file&i...xlsm", "file");
Run;
data _null_;
RC3=rename("&path.\&&file&i...xlsx",
"&path.\ERROR_&TD\&&file&i...xlsx", "file");
Run;
%END;
%mend;
%extensioncheck;
*+----------------------------------------------------------------------------------------------+
| LOOP THROUGH AND IMPORT ALL FILES IN THE MAIN FOLDER AND APPEND TO THE "ALL_IMPORT" TABLE
|
| IF THERE IS AN ERROR IMPORTING A FILE:
| MOVE IT TO THE DATED ERROR FOLDER
| OTHERWISE MOVE TO THE DATED FOLDER
+-----------------------------------------------------------------------------------------------+;
%macro importxls;
%do i=1 %to &nobs;
proc import out= work.f&i
datafile= "&PATH\&&file&i...xls"
dbms=EXCELCS REPLACE;
RANGE="Index$A3:BQ253";
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
data f&i;
set f&i;
format LoadDate date9.;
LoadDate = today();
length file_code $50;
file_code="&&file&i...xls";
proc append base=All_Import data=WORK.f&i force;
run;
%if &SYSERR = 0 %then %do;
%put &=syserr;
data _null_;
RC3=rename("&path.\&&file&i...xls",
"&path.\ERROR_&TD\&&file&i...xls", "file");
Run;
%END %ELSE %DO;
data _null_;
RC3=rename("&path.\&&file&i...xls",
"&path.\&TD\&&file&i...xls", "file");
Run;
%END;
%mend;
%importxls;
... View more