SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hoff_sas
Calcite | Level 5

Hello all together,

I am wondering whether it its possible to set a dynamic libname path.

The following example should illustrate my intention.

data _null_;

conString = compress("C:\Users\foobar\Desktop\Monthly Report\monthly_report"||&choosen_year||"_M"||&choosen_month||".xlsx");

libname smfexcel excel path=&conString;

run;

I always receive something like this " .....invalid Optionsname CONSTRING".

Any proposals or hints?

greetings

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Look closely at the suggested code, and you'll see that you omitted something from your attempt.

What is the name of your macro variable???

&smf

&smf_

&smf_year

&smf_year_M

Many other choices are possible.  How does SAS know which one to look for?  SAS is actually looking for &smf_year_M01

Delimit the name of your macro variable with a dot.  So it your macro variable is &smf, use:

smf_&smf._year_M01.xlsx


View solution in original post

6 REPLIES 6
mlamias
Fluorite | Level 6

Why not just use something like the following?

%let choosen_year=2013;

%let choosen_month=3;

libname smfexcel excel path="C:\Users\foobar\Desktop\Monthly Report\monthly_report&choosen_year._M&choosen_month..xlsx";

hoff_sas
Calcite | Level 5

That is exactly what I tired to do at first. But the variable was not interpreted.

 

libname smfexcel excel "C:\Users\...\smf_&smf_year_M01.xlsx";  set up the following library:

physical name: C:\Users\fhoff\Desktop\Monthly Report\smf_&smf_year_M01.xlsx

Because of this i started to tinker with concatenation operators.

And of course....I did some %let before the mentioned step to initialize my variables. 😉

Astounding
PROC Star

Look closely at the suggested code, and you'll see that you omitted something from your attempt.

What is the name of your macro variable???

&smf

&smf_

&smf_year

&smf_year_M

Many other choices are possible.  How does SAS know which one to look for?  SAS is actually looking for &smf_year_M01

Delimit the name of your macro variable with a dot.  So it your macro variable is &smf, use:

smf_&smf._year_M01.xlsx


mlamias
Fluorite | Level 6

The last poster is correct.  It appears you are missing the dot (.) in the your code (if I interpreted your file naming conventions correctly).  I could also see the leading zero in the month being problematic.  Note that in SAS, there is a difference between:

%let choosen_month=3;

and

%let choosen_month=03;

hoff_sas
Calcite | Level 5

Good morning....wow...this community seems to work. Very nice! Thanks to you guys.

.....the solution with the "." works. Thanks very much!

Here is the code and log results

>>libname smfexcel excel "C:\Users\fhoff\Desktop\Monthly Report\smf_&smf_year._M01.xlsx";

Physical Name: C:\Users\fhoff\Desktop\Monthly Report\smf_2013_M01.xlsx

greetings from munich

hoff_sas
Calcite | Level 5

Good morning....wow...this community seems to work. Very nice! Thanks to you guys.

.....the solution with the "." works. Thanks very much!

Here is the code and log results

>>libname smfexcel excel "C:\Users\fhoff\Desktop\Monthly Report\smf_&smf_year._M01.xlsx";

Physical Name: C:\Users\fhoff\Desktop\Monthly Report\smf_2013_M01.xlsx

greetings from munich

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 3826 views
  • 3 likes
  • 3 in conversation