I have a question for simplifying my code. I need to find out duplicate values in my data set. Since there are several data sets in the folder is there way i can use proc contents information to run . Currently i am calling several macro calls like the i called %duplicates(in=ae,seq=aeseq).
Instead of calling several "%duplicates(in=ae,seq=aeseq)' i want to see if i can simplify the code. Please suggest. Thank you
proc contents data= sdtm._all_ noprint out= sdtmall(where=(name in ('AESEQ' 'IDVARVAL'))) ; run; %macro duplicates(in=,seq=); proc sort data=sdtm.&in. out=_null_ dupout=dups__&in. nodupkey; by usubjid &seq.; run; proc print data=dups__&in. width=min ; title " Dups in sdtm.&in."; var usubjid &seq.; run; %mend; %duplicates(in=ae,seq=aeseq);
7 hours ago
Hi - I would set up some kind of loop to read through your list of data sets. Instead of proc contents, I used the dictionary.tables data set. Then in your macro you can use a %do loop to go through each data set. I've assumed that each of your sequence variables is named like the table, so you can use &in. to achieve the same thing as &seq. If that's not the case, you'd have to get creative to identify the sequence variable as well.
/*Create a list of tables in question*/ proc sql noprint; select memname into :table_list separated by ' ' from dictionary.tables where upcase(libname) eq 'WORK' and upcase(memname) in ('TEST'); quit; %let table_count = %sysfunc(countw(&table_list.)); /*Count how many need to be looped across*/ /*Begin process of identifying duplicates*/ %macro duplicates; %do i=1 %to &table_count.; %let in=%scan(&table_list.,&i.,' '); proc sort data=sdtm.&in. out=_null_ dupout=dups__&in. nodupkey; by usubjid &in.seq; run; proc print data=dups__&in. width=min ; title "Dups in sdtm.&in."; var usubjid &in.seq; run; %end; %mend; %duplicates;