SAS macro passing SQL or code to use in macro

Question

I am relatively new to SAS macro programming and I am mainly using it to make my code 'cleaner' and avoid errors in repetitive code.

I have done some googling but have not found easy way for this.

I have 2 questions first - how can I pass list of columns to macro ?

I want to have macro look something like this in general code:

proc sql;
create table &usefulTable as 
%DoStuff('col1, col3, col9', 'col1 = 12 or (col2 between 1 and 3)')
;

basically I pass column values as first parameter and where clause as second parameter.

Is it possible to make macro what will go through first argument as column names and use second argument as part of where clause ?

%MACRO DoStuff(col, cond);
    select
    &col separated by ',' ,
    'source1' as source
  from &someNiceTable
  where &cond
union
    select
    &col separated by ',' ,
    'source2' as source
  from &someNiceOtherTable
  where &cond
%mend;

Above is sample basic macro showing similar functionality I am intending to do, in this case union of 2 tables while adding new columns what defines from what table is set record. Basically there will be always applied same conditions for several tables and then they will be joined with union with one extra column added.

Would be nice to get some jelp on this.

To be honest biggest issue I am facing is with passing the were variable, because column names I can use as hardcoded values in macro, but issue is with where clause because it regularly changes.

Answer 1

You are on the right track. You are partly correct in quoting these arguments parameters as the first one (list of columns) would otherwise be interpreted as 3 separate parameters and the other one (where condition) would be interpreted as a keyword parameter named col1. However, this would require you to dequote these arguments in your macro definition. An easier way would be to use %str() in your macro call.

Your usage of separated by in your proc sql is also incorret. This is used with the into statement to store values from a select statement into macro variables.

Your macro should look like:

%MACRO DoStuff(col, cond);
    select
    &col,
    'source1' as source
  from &someNiceTable
  where &cond
union
    select
    &col,
    'source2' as source
  from &someNiceOtherTable
  where &cond
%mend;

And the call should be like this:

proc sql;
create table &usefulTable as 
%DoStuff(%str(col1, col3, col9), %str(col1 = 12 or (col2 between 1 and 3)))
;
quit;

Answer 2

If you pass your values as quoted strings and want to use them without the quotes you can call the DEQUOTE() function using the %SYSFUNC() macro function.

%MACRO DoStuff(col, cond);
select %sysfunc(dequote(&col))
     , 'source1' as source
  from &someNiceTable
  where %sysfunc(dequote(&cond))
;
%mend;

Note that this will also handle removing any macro quoting you might have used instead of literal quote characters to protect your embedded commas.

Note that for your COND variable you could just can add () around the condition instead of quotes or macro quotes to prevent the macro call from being confusing.

So your call could look like one of these:

%DoStuff(col='col1, col3, col9',cond=(col1 = 12 or (col2 between 1 and 3)))
%DoStuff(col=%str(col1, col3, col9),cond=(col1 = 12 or (col2 between 1 and 3)))

I find that it is much better to pass lists of variables with spaces between them instead of commas. This way they are directly usable in normal SAS statements. It is only SQL that makes the annoying requirement of using commas as the separator. So make the macro code add the commas so that the macro's users don't need to worry about adding them.

%macro mymac(dsn,varlist);
%local sqllist;
%let sqllist=%sysfunc(translate(%sysfunc(compbl(&varlist)),%str(,),%str( )));
proc print data=&dsn ;
  var &varlist ;
run;
proc sql ;
select &sqllist from &dsn ;
quit;
%mend mymac ;

You could also use other characters (such as | or ^) as the delimiter in your lists of values.

 %do i=1 %to %sysfunc(countw(&list,|));
   %let word=%scan(&list,&i,|);
   ...
 %end;