I have a spreadsheet name with spaces in it. I have placed this value into a macro variable.
%let standardsheet=%str(SDTM Terminology 2013-04-13$);
Now I want to use this in a proc import.
PROC IMPORT OUT= WORK.newterm
DATAFILE= "SDTM Terminology.xls"
DBMS=EXCEL REPLACE;
range="&standardsheet";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
Pretty consistently, I get something along the lines of
ERROR: File _IMEX_.'SDTM Terminology 2013-04-13$'n.DATA does not exist.
ERROR: Import unsuccessful. See SAS Log for details.
I have tried using various sets of quotes and using various macro quoting functions in my %let and I still can't get it right.
The spreadsheet does exist. The wizard proc import code is
PROC IMPORT OUT= WORK.test
DATAFILE= "SDTM Terminology.xls"
DBMS=EXCEL REPLACE;
RANGE="'SDTM Terminology 2013-04-12$'";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
Please save my sanity! I'm missing the very obvious.
The import code from the wizard has 3 quotes, one single and one double I believe.
I don't think it matters if you nest them the other way though.
It actually does matter if you nest them the other way. The error message changes, but it still doesn't work.
The code you said worked looks like this:
RANGE="'SDTM Terminology 2013-04-12$'";
So you would set your macro variable to:
%let standardsheet = SDTM Terminology 2013-04-12$ ;
And then reference like this:
RANGE="'&standardsheet'";
You might be able to remove the $ from the macro variable and add it to the reference like this:
RANGE="'&standardsheet$'";
I tried just about every combination of quotes I could think of both in and out of the macro variable.
This is a situation where if you haven't run the code and verified that it works, assume I've tried it.
I have not tried combinations of quoting functions together, although I'm not sure that would gain me anything.
So the correct answer is to have the macro variable have the correct value to start with. I knew it had to be something staring me right in the face. Sorry for taking up people's time.
%let standardsheet=%str('SDTM Terminology 2013-04-12');
and it works just fine.
Macro quoting is not needed if you have already surrounded the value with quotes.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: