How to use proc contents output to run proc sort with nodupkey

[knveraraju91]

Dear,

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);

[lrudolphi]

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;