proc sort data=have1 out=have1_sorted;
by id;run;
proc sort data=have2 out=have2_sorted;;
by id;run;
proc transpose data=have1 out=have1_trans (rename= (_name_=col_name));
by id;
var var sex ;
run;
proc transpose data=have2 out=have2_trans(rename= (_name_=col_name)) ;
by id;
var var sex sal;
run;
proc sql;
create table compare_table
as
select coalesce(a.id,b.id) as ID,coalesce(a.col_name,b.col_name) as var
, a.col1 as old_val
,b.col1 as new_val
,case when a.col1 <> b.col1 and a.id= b.id then 'Modified'
when a.col1 <> b.col1 and a.id=. then 'Added'
when a.col1 <> b.col1 and b.id=. then 'Deleted' end as remark
from have1_trans a
full join have2_trans b
on a.id = b.id and a.col_name = b.col_name;
quit; You can then export the final table to xls using proc export.
... View more
