Thank you for your response. Here is what i got.
210
211 %macro maps(d);
212 data filenames_&d. ;
213 length fref $8 fname $200;
214 did=filename(fref,"P:/Contracts/2022 Test Maps Battery Form ID/Test Maps/&d.");
215 did=dopen(fref);
216 do i=1 to dnum(did);
217 fname=dread(did,i);
218 output;
219 end;
220 did=dclose(did);
221 did=filename(fref);
222 keep fname;
223 run;
224 data filenames_&d. ;
225 set filenames_&d. ;
226 I=FIND(fname,"OE");
227 If substr(fname,I,2) ne ("OE") then delete;
228 DROP I;
229 run;
230
231 data _null_;
232 set filenames_&d.;
233 call execute(catx(' '
234 ,'proc import dbms=xlsx replace file='
235 ,quote(cats("P:/Contracts/2022 Test Maps Battery Form ID/Test Maps\",fname))
236 ,"out=",cats("testmap_&d.",_n_)
237 ,'run;'
238 ));
239 run;
240
241
242 %mend maps;
243 %maps(MAT1I);
MPRINT(MAPS): data filenames_MAT1I ;
MPRINT(MAPS): length fref $8 fname $200;
MPRINT(MAPS): did=filename(fref,"P:/Contracts/2022 Test Maps Battery Form ID/Test Maps/MAT1I");
MPRINT(MAPS): did=dopen(fref);
MPRINT(MAPS): do i=1 to dnum(did);
MPRINT(MAPS): fname=dread(did,i);
MPRINT(MAPS): output;
MPRINT(MAPS): end;
MPRINT(MAPS): did=dclose(did);
MPRINT(MAPS): did=filename(fref);
MPRINT(MAPS): keep fname;
MPRINT(MAPS): run;
NOTE: The data set WORK.FILENAMES_MAT1I has 7 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
MPRINT(MAPS): data filenames_MAT1I ;
MPRINT(MAPS): set filenames_MAT1I ;
MPRINT(MAPS): I=FIND(fname,"OE");
MPRINT(MAPS): If substr(fname,I,2) ne ("OE") then delete;
MPRINT(MAPS): DROP I;
MPRINT(MAPS): run;
NOTE: Invalid second argument to function SUBSTR at line 659 column 72.
fname=PTM_PC_PARCC_SPRING19ABO_P_MATH_Gr9_MAT1I_19M1HSSFPEB1000001_P02.XLSX I=0 _ERROR_=1 _N_=1
NOTE: Invalid second argument to function SUBSTR at line 659 column 72.
fname=PTM_PC_PARCC_SPRING21ABO_E_MATH_Gr9_MAT1I_21M1HSSFAEB1000001_P02.XLSX I=0 _ERROR_=1 _N_=2
NOTE: Invalid second argument to function SUBSTR at line 659 column 72.
fname=PTM_PC_PARCC_SPRING21ABO_E_MATH_Gr9_MAT1I_21M1HSSFHEB1000001_P02.XLSX I=0 _ERROR_=1 _N_=3
NOTE: Invalid second argument to function SUBSTR at line 659 column 72.
fname=PTM_PC_PARCC_SPRING21ABO_E_MATH_Gr9_MAT1I_21M1HSSFNEB1000001_P02.XLSX I=0 _ERROR_=1 _N_=4
NOTE: Invalid second argument to function SUBSTR at line 659 column 72.
fname=PTM_PC_PARCC_SPRING21ABO_E_MATH_Gr9_MAT1I_21M1HSSFREB1000001_P02.XLSX I=0 _ERROR_=1 _N_=6
NOTE: Invalid second argument to function SUBSTR at line 659 column 72.
fname=PTM_PC_PARCC_SPRING21ABO_E_MATH_Gr9_MAT1I_21M1HSSFTEB1000001_P02.XLSX I=0 _ERROR_=1 _N_=7
NOTE: There were 7 observations read from the data set WORK.FILENAMES_MAT1I.
NOTE: The data set WORK.FILENAMES_MAT1I has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
MPRINT(MAPS): data _null_;
MPRINT(MAPS): set filenames_MAT1I;
MPRINT(MAPS): call execute(catx(' ' ,'proc import dbms=xlsx replace file='
,quote(cats("P:/Contracts/2022 Test Maps Battery Form ID/Test Maps\",fname))
,"out=",cats("testmap_MAT1I",_n_) ,'run;' ));
MPRINT(MAPS): run;
MPRINT(MAPS): proc import dbms=xlsx replace file= "P:/Contracts/2022 Test Maps Battery Form ID/Test
Maps\PTM_PC_PARCC_SPRING21ABO_E_MATH_Gr9_MAT1I_21M1HSSFOEB1000001_P02.XLSX" out= testmap_MAT1I1 run;
NOTE: There were 1 observations read from the data set WORK.FILENAMES_MAT1I.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: CALL EXECUTE generated line.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Line generated by the CALL EXECUTE routine.
1 + proc import dbms=xlsx replace file= "P:/Contracts/2022 Test Maps Battery Form ID/Test
1 !+Maps\PTM_PC_PARCC_SPRING21ABO_E_MATH_Gr9_MAT1I_21M1HSSFOEB1000001_P02.XLSX" out= testmap_MAT1I1
1 !+run;
---
22
202
ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATAFILE, DATATABLE, DBMS, DEBUG,
FILE, OUT, REPLACE, TABLE, _DEBUG_.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
244 options mprint;
%macro maps(d);
data filenames_&d. ;
length fref $8 fname $200;
did=filename(fref,"P:/Contracts/2022 Test Maps Battery Form ID/Test Maps/&d.");
did=dopen(fref);
do i=1 to dnum(did);
fname=dread(did,i);
output;
end;
did=dclose(did);
did=filename(fref);
keep fname;
run;
data filenames_&d. ;
set filenames_&d. ;
I=FIND(fname,"OE");
If substr(fname,I,2) ne ("OE") then delete;
DROP I;
run;
data _null_;
set filenames_&d.;
call execute(catx(' '
,'proc import dbms=xlsx replace file='
,quote(cats("P:/Contracts/2022 Test Maps Battery Form ID/Test Maps\",fname))
,"out=",cats("testmap_&d.",_n_)
,'run;'
));
run;
%mend maps;
%maps(MAT1I);
options mprint;
... View more