🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
1 ACCEPTED SOLUTION
Accepted Solutions
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;
How to Concatenate Values
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
-
1642 views
-
20 likes
-
-
