DATA Step, Macro, Functions and more

Using a macro substitution in directory path names

Reply
N/A
Posts: 0

Using a macro substitution in directory path names

I'm trying to convert a sas dataset to a text file and have the name of the file be determined by a macro. Seems simple, but I cannot figure out how to include a macro in the path directory using PROC EXPORT. For example, I would like the text file to be named species.txt. Anyone have any suggestions or alternative methods? Thanks.

%let name = 'species';

proc export data=step1
outfile='C:\&name.txt'
dbms= tab
replace;
quit;
Super Contributor
Posts: 394

Re: Using a macro substitution in directory path names

Single quotes inhibit macro substitution. Use double-quotes.

outfile = "C:\&name..txt"

Also you'll need two periods in a row, the first to delimit the macro variable name and the second to actually separate species from txt.
SAS Super FREQ
Posts: 8,744

Re: Using a macro substitution in directory path names

Hi:
One of the first rules of macro programming is to use quotes appropriately and that includes an understanding of how quotes work. For example, macro variable references in single quotes are NEVER resolved. So, with single quotes around your OUTFILE string, the &name is never seen by the macro processor. One of your problems will be fixed if you changed the single quotes in OUTFILE= to double quotes. If you did this:
[pre]
%let name = 'species';

. . . more code . . .
outfile="C:\&name.txt"

[/pre]
the &name variable would resolve, but it would resolve INCORRECTLY because of the single quotes also in the %LET statement. So, the string above would resolve as: C:\'species'txt which is incorrect. And, C:\"species"txt would also be incorrect, so just changing the single quotes to double quotes in the %LET statement won't help you here.

The use of -any- quotes around the value SPECIES in the %LET statement is inappropriate. It is never a good idea to "prequote" macro variable values in a %LET statement. It is better to understand how macro variables are going to be used and quote accordingly. The way to do that is to have a working program and then modify the WORKING code to use macro variables. Allow the working code to show you where the quotes are needed.

For example, let's say I have &AMT and &REG variables:
%LET REG = Asia;
%LET AMT = 5000;

title "Report on &REG for amounts greater than &AMT";
. . . more code . . .
where region = "&REG" and sales gt &amt;

in the code above, in the WHERE statment, &REG needs to be quoted and &AMT does not need to be quoted. In the TITLE statement, the quotes belong to the TITLE statement -- neither of the macro variables should be quoted if I use them in the TITLE statement. So now, you have 2 different quoting situations for &REG. It's better to let the quotes stay in the WHERE and TITLE statements than to "prequote" the &REG value. And quotes around &AMT would just be plain WRONG in the WHERE statement -- because SALES is numeric and &AMT needs to be unquoted for the comparison.

Along those lines, you need to generate THIS as your final text:
outfile="C:\species.txt"
Note that there are no quotes in a correct OUTFILE= value. So, working from that correct file specification, we move to: outfile="C:\&name.txt" which is close, but will resolve to:
outfile="C:\speciestxt" because the single '.' after &name will act as a delimiter to the macro variable -- so what the macro processor sees is &name. as the macro variable reference and the ending . as the delimiter. This would allow you to move toward more advanced macro variable references such as:
&name.&yr..txt -- where you might want to build species2007.txt or species2008.txt as file names.

This means you will need:
[pre]
outfile="C:\&name..txt" (note the 2 dots or periods)
[/pre]


which will resolve to: outfile="C:\species.txt" because the first dot will be the delimiter for &name and the second dot will be considered part of the final, resolved string.

cynthia
N/A
Posts: 0

Re: Using a macro substitution in directory path names

Thanks, very helpful!
Ask a Question
Discussion stats
  • 3 replies
  • 155 views
  • 0 likes
  • 3 in conversation