Thanks @Tom
this is very, very close but I cannot get the %nrstr working...
I've slightly amended your code - below -
string = %nrstr(cats('%makereport(Report=',Report,',keys=',keylist,');'));
and it gives me exactly what I need
However when passing into the macro, the commas between the keys seem still to be an issue, as I receive an "All positional parameters must precede keyword parameters" error message.
I tried to move the %nrstr everywhere in the string and inside the macro, but cannot find the right way of working.
Here is the whole code for reference:
data METADATA_AUX;
input report $ Keys $;
datalines;
CARS MAKE
CARS MODEL
CARS ORIGIN
CARS TYPE
BASEBALL NAME
BASEBALL TEAM
BASEBALL DIVISION
BASEBALL LEAGUE
CLASS NAME
CLASS SEX
;
run;
PROC SQL;
CREATE TABLE WORK.METADATA AS
SELECT t1.report,
t1.Keys
FROM WORK.METADATA_AUX t1
ORDER BY t1.report;
QUIT;
PROC SQL;
CREATE TABLE WORK.POPULATION
(
Report char(7),
Tot_Dups Numeric(5)
);
QUIT;
options mprint symbolgen mlogic;
%macro makereport(Report=, keys=);
PROC SQL;
insert into WORK.POPULATION (Report, tot_dups)
SELECT "&Report" AS Report, COUNT(*) as tot_dups
FROM (
SELECT &keys, COUNT(*) AS CT
FROM SASHELP.&Report
GROUP BY &keys
HAVING CT>1
)A;
QUIT;
%mend makereport;
data want;
do until (last.report);
set METADATA end=eof;
by Report;
length keylist $1000;
keylist=catx(',',keylist,keys);
end;
string = cats(%nrstr('%makereport(Report=',Report,',keys=',keylist,');'));
call execute(string);
run;
Many thanks
... View more