Extend SAS MACRO to multiple fields -


i have macro inspired "proc sql example" finds duplicate rows based on single column/field:

data have ;  input name $ term $;  cards; joe   2000  joe   2000  joe   2002 joe   2008  sally 2001 sally 2003 ; run;   %macro dups(lib, table, groupby) ;    proc sql ;    create table duprows     select  &groupby, count(*) duplicate_rows    &lib..&table     group &groupby      having count(*) > 1      order duplicate_rows;  quit;  %mend dups ;  %dups(work,have,name) ;  proc print data=duprows ; run;  

enter image description here

i extend duplicates based on multiple columns (rows 1 , 2 in example), still flexible enough deal single column.

in case run code:

proc sql ;  create table duprows select name,term,count(*) duplicate_rows work.have group name,term having count(*) > 1 ;quit; 

to produce:

enter image description here

to include arbitrary number of fields group on, can list them in groupby macro parameter, list must comma-delimited , surrounded %quote(). otherwise sas see commas , think you're providing more macro parameters.

so in case, macro call be:

%dups(lib = work, table = have, groupby = %quote(name, term)); 

since &groupby included in select , group by clauses, fields listed appear in output , used grouping. because when &groupby resolves, becomes text name, term.


Comments

Popular posts from this blog

c++ - Difference between pre and post decrement in recursive function argument -

php - Nothing but 'run(); ' when browsing to my local project, how do I fix this? -

php - How can I echo out this array? -