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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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