topic Re: Set libname in a macro in SAS Programming

Set libname in a macro

chris2377 — Tue, 31 Oct 2017 11:33:58 GMT

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?

Re: Set libname in a macro

Kurt_Bremser — Tue, 31 Oct 2017 12:20:06 GMT

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.

Re: Set libname in a macro

chris2377 — Tue, 31 Oct 2017 12:28:23 GMT

Thanks a lot. It's working now

Re: Set libname in a macro

RW9 — Tue, 31 Oct 2017 12:29:11 GMT

Perhaps another Maxim is needed,

Always use the dot after macro variables!

Re: Set libname in a macro

Kurt_Bremser — Tue, 31 Oct 2017 12:40:51 GMT

@RW9 wrote:

Perhaps another Maxim is needed,

Always use the dot after macro variables!

Done. Maxim 48.