SAS: macro which mimic returning value from function (empty string or macro variable)

1
votes

I am new to SAS and perhaps naively trying to mimic building macros as function in SAS.

I have multiple macro variables which are initiated from a stored process. Some might have values, while others might be empty. 

%let a1 = column_name1;
%let a2 = column_name2;
%let a3 = ;

%let col1 = &a1;
%let col2 = &a2;
%let col3 = &a3;

I want to use them in proc sql as:

proc sql;
  create table some_table as
  select 
  &col1 AS column1,
  &col2 AS column2,
  &col3 AS column3
  from some_table;
quit;

However, this won't work for variables which are empty (&col3). Therefore, I am trying to build some kind of function that will be wrapper around it. Something like:

%macro macro_return_string(macro_variable);
        %if length(macro_variable) = 1 %then %do; /* if column_name# is not empty, then it len() is always >2 */
            "";
        %end;
        %else %do;
            macro_variable;
        %end;

%mend macro_return_string;

So it will be used like:

%let col1 = macro_return_string(&a1); /* return column_name1 */
%let col2 = macro_return_string(&a2); /* return column_name2 */
%let col3 = macro_return_string(&a3); /* return "" */

Thanks for help!

A similar question was asked here but I cannot solve my problem from it.

2
sassas-macro

2 Answers

2
votes

The main problem with your macro is the extra semi-colons it is emitting. If you want to make a "function" style macro then you cannot emit an unmasked semi-colon because it will terminate the command you are trying to build.

%macro macro_return_string(macro_variable);
%if 0=length(&macro_variable) %then %do; 
 " "
%end;
%else %do;
  &macro_variable
%end;
%mend macro_return_string;

Also if you sure you want to add the quotes? Are you only ever going to use this to create character variables? 

%macro macro_return_string(macro_variable,type=num);
%if 0=length(&macro_variable) %then %do; 
 %if &type=num then . else " ";
%end;
%else %do;
  &macro_variable
%end;
%mend macro_return_string;
1
votes

Macro is not a function based system like other scripting or coding languages. Macro is a text processing system, with side-effects, that may or may not emit source code for the submit system to consume.

Your macro will emit a ""; right within the sql statement you are attempting to generate, and the semicolon (;) is gumming up the works. A semi-colon in a macro may be appropriate if you have a %if with only a %then and not a %then do; … %end;

The macro for assigning a var(1) " " to a column when there is no expression in the macro argument should be:

%macro macro_return_string(macro_variable);
        %if length(&macro_variable) %then %do;/* there is something in the variable passed, resolve it for emittance as source code*/
&macro_variable/* no semi-colon here */
        %end;
        %else %do;/* argument is empty, emit a blank character as the source code for the default expression*/
" "/* no semi-colon here */
        %end;
%mend macro_return_string;