topic Re: Dynamic Libname Path in SAS Programming

Dynamic Libname Path

hoff_sas — Thu, 07 Mar 2013 16:05:11 GMT

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

Re: Dynamic Libname Path

mlamias — Thu, 07 Mar 2013 16:17:54 GMT

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";

Re: Dynamic Libname Path

hoff_sas — Thu, 07 Mar 2013 16:37:05 GMT

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. 😉

Re: Dynamic Libname Path

Astounding — Thu, 07 Mar 2013 16:59:29 GMT

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

Re: Dynamic Libname Path

mlamias — Thu, 07 Mar 2013 17:23:51 GMT

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;

Re: Dynamic Libname Path

hoff_sas — Fri, 08 Mar 2013 08:13:37 GMT

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

Re: Dynamic Libname Path

hoff_sas — Fri, 08 Mar 2013 08:14:09 GMT

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