Hi:
There are several things I don't understand:
1) why you use single quotes when you assign values to your macro variables
2) what the purpose of your data statement is.
[pre]
data initial_list;
[/pre]
Since the DATA statement is immediately followed by a PROC statement, the DATA step program is exactly 1 statement which is immediately terminated by the PROC statement (which is a step boundary).
At any rate....I created an Excel spreadsheet called:
c:\temp\Testrandom string.xls
As you can see from my SAS log, I was able to read the file with no issues:
[pre]
20 %let Learn = random string;
21 %let filenm =C:\temp\Test&Learn..xls;
22
23
24 proc import datafile="&filenm" out=work.initial_list
25 dbms=excel replace;
26 getnames=yes;
27 run;
NOTE: WORK.INITIAL_LIST was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.43 seconds
cpu time 0.18 seconds
[/pre]
Here are some comments on my code:
1) Because I assign a value to the macro variable LEARN before the assignment of a value to FILENM, there is a value for &LEARN that can be resolved when &FILENM is used.
2) As you can see, I do not use ANY quotes in the creation of my macro variables, nor do I really need them. If the assumption is that &LEARN will always be part of the file name that changes, then it is reasonable to assign a value to &LEARN before you use &LEARN in the creation of FILENM.
3) When concatenating macro variables into a string, there is a "universal" delimiter that tells SAS where a macro variable ends. That delimiter is a dot or period. When you need a period in the resolved string (such as with 2 level SAS names or a full filename/file extension), then the solution is to provide 2 dots, as shown in my assignment for FILENM (note the 2 dots before xls):
[pre]
%let filenm =C:\temp\Test&Learn..xls;
[/pre]
The first dot ends &LEARN and the second dot is the dot that's needed for the full name of the Windows file.
4) It doesn't make sense to me to put quotes around 'random string' because on my version of Windows XP I get an error message when I try to use quotes in a file name. Interestingly, I do NOT get error messages when I try to use an & in a file name, however, I really think that special characters (other than slashes and underscores and spaces) should probably be avoided as file names -- if I ever had to move these files to another operating system, those characters might not be allowed.
5) Back to the DATA statement issue. Assume that I do have a valid macro variable for &FILENM...even so, if I did this
[pre]
%let Learn = random string;
%let filenm =C:\temp\Test&Learn..xls;
data trythis;
proc import datafile="&filenm" out=work.trythis
dbms=excel replace;
getnames=yes;
run;
[/pre]
what would happen is this -- note the message that the dataset has 1 observation and 0 variables -- essentially, I have created an empty dataset that is pretty much useless. It is the PROC IMPORT that is creating the actual data set from the Excel file.
[pre]
46 data trythis;
NOTE: The data set WORK.TRYTHIS has 1 observations and 0 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
47 proc import datafile="&filenm" out=work.trythis
48 dbms=excel replace;
49 getnames=yes;
50 run;
NOTE: WORK.TRYTHIS was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.46 seconds
cpu time 0.29 seconds
[/pre]
6) Meanwhile, back at Macro variables, it looks like you really don't need quoting functions, either. Here are some examples of macro variable creation that may help you learn more about how macro variables operate. If you cut and paste this code into an editor window and submit the code, you can review what you get in the SAS log and for the title statements in the PROC PRINT output:
[pre]
%let statement = My favorite Sesame Street;
%let kf = Kermit the Frog;
%let cm = Cookie Monster;
%let bb = Big Bird;
%let num = 2;
%let mac1 = &statement characters are &kf and &cm and &bb!;
%let mac2 = But my most favorite character is &kf!;
%put ----- -----;
%put My opinion is: &mac1 &mac2;
%put ----- -----;
%put And I say num is &num: &&mac#
%put ----- -----;
%let lib=sashelp;
%let dsn=class;
proc print data=&lib..&dsn;
title "Proc Print for &lib..&dsn";
title2 "&mac1";
title3 "&&mac&num";
run;
[/pre]
cynthia