BookmarkSubscribeRSS Feed
devarayalu
Fluorite | Level 6

%macro import(path,name, out, sheet);

proc import datafile ="&path\&name.xls"

out=&out(where= (refid ~= .))

dbms=excel replace;

sheet= "&sheet";

mixed = yes;

run;

%mend import;

Suppose I want call macro with path like "d:\Clin-Dem\HCC\New Folder". How can I mask the colon in the path?

%import(%superq(d:\Clin-Dem\HCC\New Folder),%str(Cheng, Ann-Lii_2009_vol10), dataset4, Template);

5 REPLIES 5
data_null__
Jade | Level 19

You are using SUPERQ incorrectly.

I don't think the colon needs to be quoted.  You need two dots after &NAME any you may need to %UNQUOTE all that.

ballardw
Super User

And perhaps a period after &path

Here's an example that I use with multiple macro variables in an output file name:

%let  outpath= D:\Data\WIC\Nutrition Surveillance Rpt\Trend\output\ ;

ods rtf file="&outpath.district_&district._&syear._&eyear..rtf"

<options follow>

data_null__
Jade | Level 19

Don't need one after &PATH it is followed by back-splash :smileyblush:

ScottBass
Rhodochrosite | Level 12

Like data _null_ said, you don't have to quote the colon.

I suggest the following changes.  (I'm sending this from home, so don't have SAS in front of me to test):

1)  Use keyword instead of positional parameters.  It makes the syntax clearer, and IIRC removes the significance of the comma in your name parameter.

2)  If your parameters could contain tokens significant to the macro processor, you would use %superq *inside* the macro, not in the parameter declaration.

3)  If a macro variable contains text immediately following that would make the macro variable reference ambiguous, use a dot to mark the end of the macro variable reference.  So, like data _null_ said, you need two dots after &name, one to mark the end of the macro variable reference, and one as the filename extension delimiter.

4)  Most punctuation and whitespace will mark the end of a macro variable reference, so a following dot is not required.  I see some programmers "overuse" the dot to end a macro variable reference, when it's not required.  It works, but I personally find this syntax harder to read and a bit "ugly".  But that's just me.

Try this and see how it works for you:

%macro import(

   path=

   ,name=

   ,out=

   ,sheet=

);

proc import

   datafile="%superq(path)\%superq(name).xls"

   out=&out (where=(refid is not missing))

   dbms=excel

   replace;

   sheet="&sheet";

   mixed=yes;

run;

%mend;

Alternatively, you could use:

datafile="&path\&name..xls"

as long has your path or name variables aren't too funky, with characters that would confuse the macro processor.  Usually %superq() is needed in macro statements such as %if %superq(mvar) ne %str() ..., but while %superq in the above syntax may be over-cautious, it won't hurt.

%import(

   path=d:\Clin-Dem\HCC\New Folder

   ,name=Cheng, Ann-Lii_2009_vol10

   ,out=dataset4

   ,sheet=Template

);

If the comma in the name parameter confuses the macro processor, try:

name=%str(Cheng, Ann-Lii_2009_vol10)

HTH,

Scott


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 1505 views
  • 0 likes
  • 5 in conversation