Dynamic Libname Path

[hoff_sas]

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

[mlamias]

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]

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 · Accepted solution]

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]

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]

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]

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