Setting Libname inside a macro with Macro Variables

[JKrantz]

Within a macro statement that identifies all excel files in a directory location, I'm looking to declare a libname statement for each file name so that i can pull in all sheets within each excel file.

%macro fileinput();
%local i;
%do i= 1 %to &nfile;

/* Store import path and output filename into macro variables*/
data _null_;
set files(firstobs= &i obs= &i);
/* The length of fpath can't be over 201 since windows limitation */
call symput('fpath', "&indir" || filename);
call symput('farch', "&indir.&outARCH.");
call symput('foutname', tranwrd(strip(file), ' ', '_'));
run;
%PUT &fpath;
%PUT &foutname;
%put &farch;

/* Declare File for import AND libname*/

FILENAME MCC_FILE "&fpath";
libname x excel "&fpath";

/* create a table with a variable for sheetnames*/

proc sql noprint;
create table sheet as
select memname
from dictionary.members
where libname='X'
;
quit;

/* Excel file import*/

%end;

%mend;

Until my client started putting multiple sheets in one file i used the Filename statement to pull import each separate excel file.  I think this will no longer be needed after i create the libname method.  However,  when declaring the libname using 

libname x excel "&fpath";

Within the Macro statement i get this error: ERROR: Error in the LIBNAME statement.

However if i change the statement to a hard coded path: 

libname x excel "\\wilmingtonfs.corp.ad.aaamidatlantic.com\share\databasemarketing\Media Campaign\2019
Initiative\Phone Calls\MCC\MCC-TCC Call1_18 thru 12_18.xlsx";

then the program works just fine creating a table with the different sheet names within the document.  However, the document names will change each time so i have a dynamic process that pulls in the files and creates the path in a macro variable to handle such an instance. 

Can i create a libname within a macro statement using a macro variable path?

Thanks

[Reeza · Accepted solution]

What exactly is the path resolving to?

Try using the libname function instead of statement - no need for a macro variable then. 

---

@JKrantz wrote:

Within a macro statement that identifies all excel files in a directory location, I'm looking to declare a libname statement for each file name so that i can pull in all sheets within each excel file.

%macro fileinput();
%local i;
%do i= 1 %to &nfile;

/* Store import path and output filename into macro variables*/
data _null_;
set files(firstobs= &i obs= &i);
/* The length of fpath can't be over 201 since windows limitation */
call symput('fpath', "&indir" || filename);
call symput('farch', "&indir.&outARCH.");
call symput('foutname', tranwrd(strip(file), ' ', '_'));
run;
%PUT &fpath;
%PUT &foutname;
%put &farch;

/* Declare File for import AND libname*/

FILENAME MCC_FILE "&fpath";
libname x excel "&fpath";

/* create a table with a variable for sheetnames*/

proc sql noprint;
create table sheet as
select memname
from dictionary.members
where libname='X'
;
quit;

/* Excel file import*/

%end;

%mend;

Until my client started putting multiple sheets in one file i used the Filename statement to pull import each separate excel file.  I think this will no longer be needed after i create the libname method.  However,  when declaring the libname using 

libname x excel "&fpath";

Within the Macro statement i get this error: ERROR: Error in the LIBNAME statement.

However if i change the statement to a hard coded path: 

libname x excel "\\wilmingtonfs.corp.ad.aaamidatlantic.com\share\databasemarketing\Media Campaign\2019
Initiative\Phone Calls\MCC\MCC-TCC Call1_18 thru 12_18.xlsx";

then the program works just fine creating a table with the different sheet names within the document.  However, the document names will change each time so i have a dynamic process that pulls in the files and creates the path in a macro variable to handle such an instance. 

Can i create a libname within a macro statement using a macro variable path?

Thanks

---

[JKrantz]

Thank you Reeza.  To answer your question, my &fpath would resolve to "\\wilmingtonfs.corp.ad.aaamidatlantic.com\share\databasemarketing\Media Campaign\2019
Initiative\Phone Calls\MCC\MCC-TCC Call1_18 thru 12_18.xlsx" in this partiuclar case,  However the xlsx file name changes for each file in a particular directory.  I pulled the name directly out of the %put statement in the log and hard coded it and it work, where as the use of &fpath in the libname statement did not.  

However, I was not familiar with Libname Function... A quick search landed me with this code where i substituted my names.  I am happy to say it works!!!! 

%if (%sysfunc(libname(x,&fpath))) %then
%put %sysfunc(sysmsg());

I now have a table of Sheet names for a dynamically set excel document in my file directory... The next plan is to look through the sheet names and pull in the data... Thank you for all of your help!!!

[Reeza]

Once you have the library set up, use PROC COPY to copy all the datasets over easily.

proc copy in=Excel out=work;
run;

You can use a data _null_ step with a call execute or dosubl to run that after you create the library.

[Kurt_Bremser]

Create a macro to handle a single Excel file dynamically:

%macro excel_import(fpath);

FILENAME MCC_FILE "&fpath";
libname x excel "&fpath";

/* create a table with a variable for sheetnames*/

proc sql noprint;
create table sheet as
select memname
from dictionary.members
where libname='X'
;
quit;

/* Excel file import*/

%mend;

Then, call that macro off your dataset:

data _null_;
set files;
fpath = "&indir" || filename;
call execute('%nrstr(%excel_import(' !! trim(fpath) !! '))');
run;

If you need to hand further parameters to the macro, expand the definition and the call accordingly.