Set libname in a macro

[chris2377]

Hi,

In a given catalogue, I have a number of xlsm files that I want to import to SAS using libname excel. The files are named sales_2013.xlsm, sales_2014.xlsm etc.

I've tried the following loop

options mprint mlogic symbolgen;
%macro set_libname;
%do i=2013 %to 2016;
	options validvarname=any validmemname=extend;
	libname scen&i excel "C:\Data\sales_&i.xlsm" access=read;
%end;
%mend;
%set_libname; 

However, the code produces the following error: "ERROR: ERROR: File extension specified is invalid". In the log I can see that SAS resolves the path to e.g. "C:\Data\sales_2013". So, it ignores the ".xlsm" extension, which - I guess - causes the problem.

How can I fix it?

[Kurt_Bremser · Accepted solution]

Note that a dot is used to terminate macro variable names, which is especially needed when macro variables are used within a string:

libname scen&i excel "C:\Data\sales_&i..xlsm" access=read;

The first dot terminates the variable call &i, the second one will appear in the resulting filename.

[chris2377]

Thanks a lot. It's working now

[RW9]

Perhaps another Maxim is needed, 

Always use the dot after macro variables!

[Kurt_Bremser]

---

@RW9 wrote:

Perhaps another Maxim is needed, 

Always use the dot after macro variables!

---

Done. Maxim 48.