BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buckeyefisher
Obsidian | Level 7

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)  

1 ACCEPTED SOLUTION
4 REPLIES 4
ballardw
Super User

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
Obsidian | Level 7
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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1018 views
  • 5 likes
  • 3 in conversation