topic Re: Importing multiple Excel files in SAS Enterprise Guide

Importing multiple Excel files

buckeyefisher — Fri, 05 Mar 2021 23:08:37 GMT

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)

Re: Importing multiple Excel files

ballardw — Sat, 06 Mar 2021 00:14:06 GMT

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";*/

Re: Importing multiple Excel files

buckeyefisher — Sat, 06 Mar 2021 15:46:09 GMT

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.

Re: Importing multiple Excel files

Kurt_Bremser — Sat, 06 Mar 2021 18:29:37 GMT

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

Re: Importing multiple Excel files

Kurt_Bremser — Sat, 06 Mar 2021 21:42:58 GMT

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;