Importing multiple Excel files

[buckeyefisher]

I have been using following code to import multiple excel files. But today its giving me this error - ERROR: File WORK.RUN.DATA does not exist. Tries looking up here but unable to resolve. Following is the code 

----------------------------------------------------------------------------------------

options mprint mlogic symbolgen;

%macro merge(n); 
%do i=1 %to &n;
proc import out=datafile&i datafile="&&file&i" dbms=xlsx replace;
*sheet="xyz";
getnames=yes;
run;

%end;

data whole;
set %do j=1 %to &n;
datafile&j
%end;
run;

%mend merge;

filename lib pipe 'dir "E:\abc\*.xlsx" /b';
data _null_;
infile lib;
input;list;
run;

data file;
length filenames $ 200;
infile lib truncover;
input filenames : $;
filenames="E:\abc\"||filenames;
run;

data null;
set file;
call symputx('file'||put(_n_,8. -L ),filenames,'G');
run;

options sasautos=work;
%merge(10)  

[ballardw]

You have option MPRINT  MOLOGIC and SYMBOLGEN turned on. Good.

Try using only fixing so that you are only reading two files as the parameter for the macro to reduce the length of the log.

Show the LOG from running the macro. All of it. Copy and paste into a text box opened on the forum with the </> icon.

Most likely something is causing this:

data whole;
set %do j=1 %to &n;
   datafile&j
%end;
run;

to cause datafile&j to be undefined for all the j=1 %to &n.

Which may point to a problem with

filename lib pipe 'dir "E:\abc\*.xlsx" /b'; not finding any files the folder.

BTW you should not use this style of comment inside macros:

*sheet="xyz";

as they still get passed to the macro processor and sometimes "comments" get executed or mess with other code.

use one of

%*sheet="xyz";

/*sheet="xyz";*/

[buckeyefisher]

SYMBOLGEN:  Macro variable SASWORKLOCATION resolves to 
            "C:\Users\AppData\Roaming\SAS\EnterpriseGuide\EGTEMP\SEG-19596-7410e0f1\contents\SAS Temporary 
            Files\_TD32504_MGTAWATE18D_\Prc2/"
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                      
1         pubmed21n0060.xlsx 18
2         pubmed21n0061.xlsx 18
MLOGIC(MERGE):  Beginning execution.
MLOGIC(MERGE):  Parameter N has value 2
SYMBOLGEN:  Macro variable N resolves to 2

MLOGIC(MERGE):  %DO loop beginning; index variable I; start value is 1; stop value is 2; by value is 1.  
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable FILE1 resolves to E:\abc\pubmed21n0060.xlsx
MPRINT(MERGE):   proc import out=datafile1 datafile="E:\abc\pubmed21n0060.xlsx" dbms=xlsx replace;
MPRINT(MERGE):   RXLX;
MPRINT(MERGE):   getnames=yes;
MPRINT(MERGE):   run;


MLOGIC(MERGE):  %DO loop index variable I is now 2; loop will iterate again.
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  Macro variable FILE2 resolves to E:\abc\pubmed21n0061.xlsx
MPRINT(MERGE):   proc import out=datafile2 datafile="E:\abc\pubmed21n0061.xlsx" dbms=xlsx replace;
MPRINT(MERGE):   RXLX;
MPRINT(MERGE):   getnames=yes;
MPRINT(MERGE):   run;

MLOGIC(MERGE):  %DO loop index variable I is now 3; loop will not iterate again.
MPRINT(MERGE):   data whole;
SYMBOLGEN:  Macro variable N resolves to 2
MLOGIC(MERGE):  %DO loop beginning; index variable J; start value is 1; stop value is 2; by value is 1.  
SYMBOLGEN:  Macro variable J resolves to 1
MLOGIC(MERGE):  %DO loop index variable J is now 2; loop will iterate again.
SYMBOLGEN:  Macro variable J resolves to 2
MLOGIC(MERGE):  %DO loop index variable J is now 3; loop will not iterate again.
MPRINT(MERGE):   set datafile1 datafile2 run;
ERROR: File WORK.RUN.DATA does not exist.
MLOGIC(MERGE):  Ending execution.
77         ;*';*";*/;quit;run;
                     ____
                     180
ERROR 180-322: Statement is not valid or it is used out of proper order.

[Kurt_Bremser]

Your SET statement misses a terminating semicolon, so the word run is considered to be the name of a dataset.

[Kurt_Bremser · Accepted solution]

Write your code in a manner that makes data step code vs. macro code visible:

data whole;
set
%do j=1 %to &n;
  datafile&j
%end;
;
run;