topic Re: Setting Libname inside a macro with Macro Variables in SAS Programming

Setting Libname inside a macro with Macro Variables

JKrantz — Wed, 06 Mar 2019 21:45:05 GMT

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

Re: Setting Libname inside a macro with Macro Variables

Reeza — Thu, 07 Mar 2019 01:56:42 GMT

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

Re: Setting Libname inside a macro with Macro Variables

Kurt_Bremser — Thu, 07 Mar 2019 06:56:29 GMT

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.

Re: Setting Libname inside a macro with Macro Variables

JKrantz — Thu, 07 Mar 2019 16:42:18 GMT

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!!!

Re: Setting Libname inside a macro with Macro Variables

Reeza — Fri, 08 Mar 2019 20:43:19 GMT

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.