file export macro with rename option

[kb011235]

Hello,

I have a saved macro that I use for exporting...

%macro xport (lib,tbl) ;

proc export data = &lib..&tbl.
  outfile = "/sasuser/bailkx4/sasdata/export/&tbl..txt"
  dbms = tab
  replace ;
  run ;
  
%mend xport ;

...and recently I was testing a SAS program where it would have been nice to be able to export the dataset with a new name after each procedure in order to see how it changed between each step.

I tried modifying the macro to ...

%macro xport (lib,tbl,rename) ;

proc export data = &lib..&tbl.
  %if rename NE '' %then
  outfile = "/sasuser/bailkx4/sasdata/export/&rename..txt";
  %Else
  outfile = "/sasuser/bailkx4/sasdata/export/&tbl..txt";
  dbms = tab
  replace ;
  run ;
  
%mend xport ;

...but when I tried exporting without indicating the rename argument (%xport(work,data)), the file name came out as ".txt" with no name. My intent was that the rename argument would be optional, so if nothing is passed into the rename argument, the default would be to name the file tbl. If the rename argument is passed into the macro, it works as intended. I would like to retain the original functionality, but with the additional capability of renaming the dataset.

Any help to solve this issue would be much appreciated. Thanks.

[Reeza · Accepted solution]

1. you're mising an & before rename in your %IF statement.
2. Use %STR() instead to check for a blank value. 3. Make sure the semicolon ends up where it needs to be as well - the out at the end of the outfile statement.

[kb011235]

1. Thanks for catching the missing &. As soon as I added that to the macro, it worked as intended
2. I'm not following why to use the STR() function.

[ballardw]

You should provide an example of the actual macro call that misbehaved in general.

If the code is as you posted:

%if rename NE '' %then

is ALWAYS true because the text rename is not ''

%macro dummy(rename);
   %if &rename ne '' %then %put Rename was: &rename;
%mend;

%dummy(word)
%dummy( )

You probably meant to use

%if &rename NE '' %then

so the resolved value of the rename macro variable is used for the comparison. But that has issues because blank is also not equal to ''. Example:

%macro dummy(rename);
   %if &rename ne '' %then %put Rename was: &rename;
%mend;

%dummy(word)
%dummy( )

Testing for blanks is slightly more complex (stolen from I don't remember where)

/* macro to test macro parameters             */
/* returns 1 if the tested parameter is blank */
/* 0 otherwise, blank means all charaters are,*/
/* or are macro variables that resolve to a,  */
/* blank                                      */
/* param can be upto 65,531 characters long   */
/* if numeric and several 1000 digits long may*/
/* hang the session. (Windows 32 bit OS)      */
/* NOT a test for a NULL (zero length string) */
/* though may work for some of those as well  */

%macro isBlank(param);
%sysevalf(%superq(&param)=,boolean)
%mend isBlank;

And use as

%if %isblank(&rename) = 0 %then <your code for when the parameter is not blank>

[kb011235]

@ballardw Thanks for your explanation. The dummy example was helpful and I really like the isblank macro. I'll be adding that one to my toolbox.

[Tom]

I usually just use %LENGTH() to test for empty parameters.

%macro xport (lib,tbl,filename) ;
%if not %length(&filename) %then %let filename=%sysfunc(lowcase(&tbl));

proc export data = &lib..&tbl.
  outfile = "/sasuser/bailkx4/sasdata/export/&filename..txt"
  dbms = tab
  replace 
;
run ;
  
%mend xport ;

[kb011235]

Very clever, thank you!