How do I quote the darned thing?

[SusanParker]

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.

[Reeza]

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.

[SusanParker]

It actually does matter if you nest them the other way.  The error message changes, but it still doesn't work.

[Tom]

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

[SusanParker]

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.

[SusanParker]

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.

[Tom]

Macro quoting is not needed if you have already surrounded the value with quotes.