topic How do I quote the darned thing? in SAS Programming

How do I quote the darned thing?

SusanParker — Fri, 19 Apr 2013 20:10:37 GMT

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.

Re: How do I quote the darned thing?

Reeza — Fri, 19 Apr 2013 20:18:59 GMT

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.

Re: How do I quote the darned thing?

Tom — Fri, 19 Apr 2013 20:51:41 GMT

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

Re: How do I quote the darned thing?

SusanParker — Fri, 19 Apr 2013 21:23:20 GMT

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

Re: How do I quote the darned thing?

SusanParker — Fri, 19 Apr 2013 21:24:55 GMT

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.

Re: How do I quote the darned thing?

SusanParker — Fri, 19 Apr 2013 21:31:50 GMT

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.

Re: How do I quote the darned thing?

Tom — Fri, 19 Apr 2013 22:15:00 GMT

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