Select variables using macro

[Tom]

In normal SAS code you use spaces between items in a list:

proc print data=sashelp.class;
   var name age sex ;
run;

In SQL code you frequently need to use commas between the items in a list

proc sql;
  select name, age, sex 
  from sashelp.class 
  ;
quit;

When calling a macro you use commas between the parameters to the macro.

%makereport(Report=cars, Keys=model)

So to specify multiple keys you should use spaces between the names in your macro call.

%makereport(Report=cars, Keys=make model)

You can add the logic to insert the commas into the macro definition so the list of keys can be used to generate valid SQL code.

%macro makereport(Report=, Keys=);
%local keys_sql ;
%let keys_sql=%sysfunc(translate(%sysfunc(compbl(&keys)),%str(,),%str( )));
PROC SQL;
insert into WORK.POPULATION (report,tot_dups)
  SELECT "&Report",COUNT(*)
  FROM (
    SELECT &Keys_sql, COUNT(*) AS CT
      FROM &Report
      GROUP BY &KEYS_sql
      HAVING CT>1
    ) A
;
QUIT;
%mend makereport;

Then you datastep to generate the call can be something like:

data _null_
  set METADATA;
  by report;
  if first.report then call execute(cats('%makereport(REPORT=SASHELP.',Report,',Keys='));
  call execute(trim(keys)||' ');
  if last.report then call execute(')');
run;

[MART1]

thanks @Tom 

I now understand how it should be done. Thanks for your code  (it's giving some errors though, even with the options enables I cannot quite figure how where the error is).

The data step seems ok (I've run it in pieces without the "execute" and they all run fine ) so it must be within the PROC SQL, but cannot see where - below is the log.

is there any other option that would give more info about the error (I'm talking as a complete novice here; probably very easy to spot to a trained eye, but I've just started with macros).

SAS1.pngSAS2.png

many thanks

[Tom]

Try delaying the macro processor from acting on the macro call by using %NRSTR() macro quoting.

Instead of using

%makereport

use 

%nrstr(%makereport)

Otherwise revert to generating the string into data step variable and then issuing just one call execute statement.