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;
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).
many thanks
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: