SAS macro passing SQL or code to use in macro

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.

Solution

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;