data have1;
input id var $ record sex $ sal fold $ rate;
datalines;
10 bala 1 m 200 v1 5
10 bala 2 m 300 v1 4
10 bala 3 f 200 a2 6
11 sri 1 m 204 d2 2
11 sri 2 f 408 d4 3
12 koti 1 m 490 v3 4
12 koti 3 f 300 b3 5
12 koti 5 m 203 n4 3
;
run;
data have2;
input id var $ record sex $ sal fold $ rate;
datalines;
10 bala 1 f 220 v1 5
10 bala 2 m 300 v1 4
10 bala 3 m 230 a2 7
11 sri 1 m 204 v2 2
11 sri 2 m 468 d4 3
12 koti 1 f 491 n3 4
12 koti 3 m 300 b3 5
12 koti 5 f 203 n4 3
12 koti 6 m 213 n4 3
;
run;
%let key_col=id var record ;
%let compare_col=sex sal fold rate;
%let key_col_cnt=%sysfunc(countw(&key_col));
%macro select_col;
%do i=1 %to &key_col_cnt;
%let col=%sysfunc(scan( &key_col,&i));
coalesce(a.&col,b.&col) as &col,
%end;
%mend;
%macro join_cond;
%do i=1 %to &key_col_cnt;
%let col=%sysfunc(scan( &key_col,&i));
a.&col =b.&col and
%end;
%mend;
proc sort data=have1 out=have1_sorted;
by &key_col ;run;
proc sort data=have2 out=have2_sorted;;
by &key_col ;run;
proc transpose data=have1 out=have1_trans (rename= (_name_=col_name));
by &key_col ;
var &compare_col ;
run;
proc transpose data=have2 out=have2_trans(rename= (_name_=col_name)) ;
by &key_col ;
var &compare_col;
run;
proc sql;
create table compare_table
as
select %select_col
coalesce(a.col_name,b.col_name) as variable_name
,strip(a.col1) as old_val
,strip(b.col1) as new_val
,case when strip(a.col1) <> strip(b.col1) and a.id= b.id and a.record =b.record then 'Modified'
when strip(a.col1) <> strip(b.col1) and a.id=. then 'Added'
when strip(a.col1) <> strip(b.col1) and b.id=. then 'Deleted' end as remark
from have1_trans a
full join have2_trans b
on %join_cond
a.col_name = b.col_name;
quit;
Automated the above code using two macro variable key_col and compare_col. User need to supply value to these two macro variables and proc sql changes would be handled itself. %let key_col=id var record ;
%let compare_col=sex sal fold rate; Here there is an assumption key_col and compare_col columns should be present both in have1 and have2.
... View more
