DATA Step, Macro, Functions and more

Set libname in a macro

Accepted Solution Solved
Reply
Contributor
Posts: 67
Accepted Solution

Set libname in a macro

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?

 


Accepted Solutions
Solution
‎10-31-2017 08:28 AM
Super User
Posts: 9,888

Re: Set libname in a macro

[ Edited ]
Posted in reply to chris2377

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎10-31-2017 08:28 AM
Super User
Posts: 9,888

Re: Set libname in a macro

[ Edited ]
Posted in reply to chris2377

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 67

Re: Set libname in a macro

Posted in reply to KurtBremser
Thanks a lot. It's working now
Super User
Super User
Posts: 9,415

Re: Set libname in a macro

Posted in reply to KurtBremser

Perhaps another Maxim is needed, 

Always use the dot after macro variables!

Super User
Posts: 9,888

Re: Set libname in a macro


RW9 wrote:

Perhaps another Maxim is needed, 

Always use the dot after macro variables!


Done. Maxim 48.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 629 views
  • 2 likes
  • 3 in conversation