BookmarkSubscribeRSS Feed
SusanParker
Calcite | Level 5

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.

6 REPLIES 6
Reeza
Super User

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
Calcite | Level 5

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

Tom
Super User Tom
Super User

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
Calcite | Level 5

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
Calcite | Level 5

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
Super User Tom
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1015 views
  • 0 likes
  • 3 in conversation