100 datasets in library... Are any of them duplicates?

[Patrick]

@NKormanik 

So if you want "me" to even take a stab at some code please provide the requirements and define what needs to be considered "the same" for tables to become candidates for a Proc Compare (which then actually looks at the data).

Suggested:

1. Identical column names (case insensitive) and type (char or num) - ignore formats, informats, labels and other stuff

2. Same number of rows

3. Indexes and Constraints: Eventually ignore - please decide! (I'd go at least for a first installment for "ignore" as else this would add complexity to the code).

[Patrick · Accepted solution]

@NKormanik 

Below fully working code (you can run it in your environment) should give you many pointers how you could go about this.

I was too lazy to add much comment so you will need to get your head around how this is working. 

/** create sample data **/
options dlcreatedir;
libname src_lib "%sysfunc(pathname(work))/data_source";

proc datasets lib=src_lib nolist mt=data;
  copy
    in=sashelp
    out=src_lib;
    ;
  run;
quit;

data src_lib.class2 src_lib.class3;
  set sashelp.class;
run;

data src_lib.class4;
  set sashelp.class;
  age=10;
run;

data src_lib.class5;
  set sashelp.class;
  if _n_=1 then output;
  output;
run;

data src_lib.class6;
  set sashelp.class;
  new_var='a';
run;

/** and here all the logic **/
proc sql;
  create table work.tbl_and_cols as
  select libname, memname, upcase(name) as name, type
  from dictionary.columns
  where libname='SRC_LIB'
  order by memname, name
  ;
quit;


data work.table_meta;
  set work.tbl_and_cols;
  by memname name;
  length col_name_digest col_type_digest $32;
  retain col_name_digest col_type_digest;
  col_name_digest=put(md5(catx('|',col_name_digest,name)),$hex32.);
  col_type_digest=put(md5(catx('|',col_type_digest,type)),$hex32.);
  if last.memname then 
    do;
      output;
      call missing(col_name_digest, col_type_digest);
    end;
run;

proc sql;
  create table work.comp_candidates as
  select 
    l.libname as libname,
    l.memname as l_memname,
    r.memname as r_memname
  from work.table_meta l inner join work.table_meta r
  on 
    l.col_name_digest=r.col_name_digest and
    l.col_type_digest=r.col_type_digest and
    l.memname > r.memname
  ;
quit;

%macro comp(libref,base,comp);
  proc compare
    base=&libref..&base
    comp=&libref..&comp
    noprint
    out=work.__comp_result
    OUTNOEQUAL
    ;
  run;
  data work.__ds_no_diff;
    if nobs=0 then
      do;
        if 0 then set work.comp_candidates;
        libname="&libref";
        l_memname="&base";
        r_memname="&comp";
        keep libname l_memname r_memname;
        output;
      end;
    stop;  
    set work.__comp_result nobs=nobs;
  run;
  proc append base=work.ds_no_diff data=work.__ds_no_diff;
  run;quit;

%mend;

proc datasets lib=work nolist nowarn;
  delete __comp_result __ds_no_diff ds_no_diff;
  run;
quit;

data _null_;
  set work.comp_candidates;
  length cmd $100;
  cmd=cats('%comp(',libname,',',l_memname,',',r_memname,')');
  call execute(cmd);
run;

proc datasets lib=work nolist nowarn;
  delete __comp_result __ds_no_diff;
  run;
quit;

/** print result: Pairs of identical tables **/
proc print data=work.ds_no_diff;
run;

[NKormanik]

@Patrick   That's some beautiful code!