DATA Step, Macro, Functions and more

How do I quote the darned thing?

Reply
Contributor
Posts: 37

How do I quote the darned thing?

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.

Super User
Posts: 19,789

Re: How do I quote the darned thing?

Posted in reply to SusanParker

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.

Contributor
Posts: 37

Re: How do I quote the darned thing?

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

Super User
Super User
Posts: 7,042

Re: How do I quote the darned thing?

Posted in reply to SusanParker

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

Contributor
Posts: 37

Re: How do I quote the darned thing?

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.

Contributor
Posts: 37

Re: How do I quote the darned thing?

Posted in reply to 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.

Super User
Super User
Posts: 7,042

Re: How do I quote the darned thing?

Posted in reply to SusanParker

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

Ask a Question
Discussion stats
  • 6 replies
  • 288 views
  • 0 likes
  • 3 in conversation