Hi,
I am trying to import some excel files. I first listed the filenames. Now I am trying to import those files that I listed. I think the last part is not correct but not sure where to edit.
Thanks,
%macro maps(d);
data filenames_&d. ;
length fref $8 fname $200;
did=filename(fref,"Z:/Tests/&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;
%do j=1 %to &&fname;
data _null_;
set filenames_&d.;
if _n_=&j;
call symput ('fname',fname);
run;
proc import file="Z:/Tests/&d.\&fname"
out=testmap_&d.
dbms=xlsx;
run;
%end;
%mend maps;
%maps(MAT1I);
You have to import each file to a different SAS dataset name, otherwise only either the first or last one will exist (depending on whether or not you added the REPLACE option to the PROC IMPORT code).
You could perhaps just add a numeric suffix to TESTMAP&D. to make the names unique.
If by the "last part" you mean the %DO loop then remove it.
To generate code from data you can just use a data step instead.
data _null_;
set filenames_&d.;
call execute(catx(' '
,'proc import dbms=xlsx replace file='
,quote(cats("Z:\Tests\&d.\",fname))
,"out=",cats("testmap_&d.",_n_)
,'run;'
));
run;
@dustychair wrote:
I think the last part is not correct but not sure where to edit.
This is never sufficient. Saying something is "not correct" and providing no additional information about what happens that is "not correct" gives us nothing to go on, and is to be avoided. Explaining and showing us would move things much closer to a solution. Please do this every time from now on. Please do not ever tell us it is "not correct" without explanation and evidence.
If there are errors or other problems in the log, then SHOW US the entire log for this macro. We need to see every single line of the log for this macro, not selected parts. In addition, since this is a macro, please turn on the macro debugging by running this line of code, and then run the macro again.
options mprint;
Please copy the log as text and paste it into the window that appears when you click on the </> icon.
If there are no problems in the log, but the output of the macro is not correct, then SHOW US what you see, and explain what it is that you want to see.
You have to import each file to a different SAS dataset name, otherwise only either the first or last one will exist (depending on whether or not you added the REPLACE option to the PROC IMPORT code).
You could perhaps just add a numeric suffix to TESTMAP&D. to make the names unique.
If by the "last part" you mean the %DO loop then remove it.
To generate code from data you can just use a data step instead.
data _null_;
set filenames_&d.;
call execute(catx(' '
,'proc import dbms=xlsx replace file='
,quote(cats("Z:\Tests\&d.\",fname))
,"out=",cats("testmap_&d.",_n_)
,'run;'
));
run;
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;
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_.
Your log makes it clear you are missing a semicolon. The code shown will not run until you add in the semi-colon.
I see but i don't know where to put the semicolon. I tried many locations but did not work.
Learn to use the SAS documentation. As I'm sure you realise, guessing is not productive.
First, I suggest you bookmark the above web link.
Second, once you are at the SAS documention main page, select the SAS 9.4 programming option: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/pgmsashome/home.htm
Third, select the search option and type in PROC IMPORT and press search. You should soon be able to find what you are looking for.
Hint: The RUN statement is a separate statement in SAS, telling it to execute the preceding code. Place a semicolon just before RUN like this ==> ;run;
This will close any previous statement.
Even bigger hint - see what I've done on line 237.
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;
@dustychair wrote:
I see but i don't know where to put the semicolon. I tried many locations but did not work.
First, you need to figure out where the semi-colon goes to make the code work, without macros. The code produced as shown in the log is
proc import dbms=xlsx replace file=
"P:/Contracts/2022 Test Maps Battery Form ID/TestMaps\PTM_PC_PARCC_SPRING21ABO_E_MATH_Gr9_MAT1I_21M1HSSFOEB1000001_P02.XLSX"
out= testmap_MAT1I1
run;
Where do you put the semicolon to make the above work?
First thing, your Fname will include values that are the names of subfolders if there are any. If any of those happen to have "OE" in the names then bad things are expected.
You have a couple of potential problems with this:
I=FIND(fname,"OE"); If substr(fname,I,2) ne ("OE") then delete;
First is case of the returned fname values. Windows filenames can be mixed case. So you will not find "oE", "oe" or "Oe" spellings that the OS will return.
Second, if not found the Find function returns 0. The Substr function will complain about invalid parameters and the results are problematied for the IF in that case. Since you are not actually using I anywhere else you could use
if find(fname,"OE",'i') = 0 then delete.
The 'i' parameter tells Find to do case insensitive comparisons, then deletes when not found.
Next, you apparently expect to import XLSX files. Your code does not check for the existence much less the value of any extensions.
A general comment about Proc Import and multiple XLSX files in general: Import guesses properties for columns for every single separate file. So the same column in different import results may be of a different type or length. These two can cause serious problems if you ever intend to combine the data. If your data sources are as messy as some spreadsheets then you can even have issues with the variable names changing that you expect to be the same in multiple files.
@dustychair Here some code similar to what you've done but without the need of macro coding. You can of course still "wrap" a macro around it if you want to make it a re-usable autocall macro.
%let path=c:\temp;
%let ext=xlsx;
%let exclusion_string=;
%let inclusion_string=oe;
/* create directory listing */
/* code based on: https://www.lexjansen.com/wuss/2012/55.pdf */
data dirlist;
keep file_nm;
length fref $8 file_nm $80;
rc = filename(fref, "&path");
if rc = 0 then
do;
did = dopen(fref);
rc = filename(fref);
end;
else
do;
length msg $200.;
msg = sysmsg();
put msg=;
did = .;
end;
if did <= 0 then
putlog 'ERR' 'OR: Unable to open directory.';
dnum = dnum(did);
do i = 1 to dnum;
file_nm = dread(did, i);
/* If this entry is a file, then output. */
fid = mopen(did, file_nm);
if fid > 0 then
do;
if "&ext"="" or lowcase(scan(file_nm,-1,'.'))=%lowcase("&ext")
then
do;
output;
end;
end;
end;
rc = dclose(did);
run;
/* create valid SAS table names and ensure names are unique */
data out_tables;
set dirlist;
if "&exclusion_string" ne " " and find(file_nm,"&exclusion_string",'i')>0 then delete;
if "&inclusion_string" ne " " and find(file_nm,"&inclusion_string",'i')<=0 then delete;
if "&ext" = " " then outname=file_nm;
else outname=substr(file_nm,1,sum(length(file_nm),-length(".&ext")));
outname=prxchange('s/(^[0-9])/_$1/i',-1,strip(outname));
outname=prxchange('s/[^0-9a-z]+/_/i',-1,strip(outname));
outname=substrn(outname,1,32);
outname=lowcase(outname);
run;
proc sort data=out_tables;
by outname;
run;
data out_tables_2;
set out_tables;
by outname;
if first.outname then _cnt=1;
if not (first.outname and last.outname) then
do;
outname=cats(substrn(outname,1,30),'_',put(_cnt,f1. -l));
_cnt+1;
end;
run;
/* import Excel files */
%let sv_validvarname=%sysfunc(getoption(validvarname,keyword));
options validvarname=v7;
data _null_;
set out_tables_2;
length cmd $500;
cmd=
catt
(
"proc import file='&path/",file_nm,"'",
" out=",outname,
" dbms=xlsx replace;",
"run;"
);
call execute(cmd);
run;
options &sv_validvarname;
@Patrick Thanks. It is working but it is pulling all of the excel files. I want only excel files which has "OE" in the name. It needs to be mofified.
@dustychair wrote:
@Patrick Thanks. It is working but it is pulling all of the excel files. I want only excel files which has "OE" in the name. It needs to be mofified.
I sort-of feel that's a simple modification you could have added yourself ...but o.k., code previously posted amended with additional requirement.
Please note: String exclusions/inclusions are NOT case sensitive. IF you want them case sensitive then remove option 'i' in the find command.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.