Yes. I did. but it does not work. how to put this flag to corresponding records(updated, added and deleted) onto this NEW's dataset? Could you please help me to look this? Thank you in advance!
data work.OLD ;
infile datalines dsd dlm='|' truncover;
input name :$12. age subject :$8. folderseq RecordPosition term :$48.
rate
;
datalines4;
Frank|20|301001|1|1|Headache|5
Frank|20|301001|1|2|pain|5
Annie|31|301002|2|1|abc|4
Annie|31|301002|2|2|abcd|4
Jessica|38|301005|5|0|a1|3
;;;;
data work.NEW ;
infile datalines dsd dlm='|' truncover;
input name :$12. age subject :$8. folderseq RecordPosition term :$48.
rate
;
datalines4;
Frank|21|301001|1|1|headache|6
Frank|20|301001|1|2|pain|5
Annie|31|301002|2|1|abcd|4
Annie|31|301002|2|2|abcd|4
Andy|40|301004|4|0|b1|3
;;;;
data have1;
set OLD;
run;
data have2;
set NEW;
run;
%let key_col=subject folderseq RecordPosition;
/* %let compare_col=name age sex rate; */
%let key_col_cnt=%sysfunc(countw(&key_col));
/* Code to find compare column list - START */
proc contents data=have2 out=contents2 noprint;
run;
%macro where_cond;
%do i=1 %to &key_col_cnt;
%if &i < &key_col_cnt %then
%do;
%let col=%sysfunc(scan( &key_col,&i));
"&col" ","
%end;
%else
%do;
%let col=%sysfunc(scan( &key_col,&i));
"&col" ", "
%end;
%end;
%mend;
proc sql noprint;
select name into :compare_col separated by ' '
from contents2
where name not in (%where_cond);
quit;
%put COMPARE COLUMN = &compare_col ;
/* Code to find compare column list - END */
%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.subject= b.subject and a.folderseq =b.folderseq then 'Modified'
when strip(a.col1) <> strip(b.col1) and (a.subject='' or a.folderseq=.) then 'Added'
when strip(a.col1) <> strip(b.col1) and (b.subject='' or b.folderseq=.) then 'Deleted' end as remark
from have1_trans a
full join have2_trans b
on %join_cond
a.col_name = b.col_name;
quit;
Data compare_table_2;
set compare_table;
if remark in ("Added") then newval=strip(strip(new_val)||"|"||"Added");
else if remark in ("Deleted") then newval=catx('|',"@","Deleted",old_val);
else if remark in ("Modified") then newval=catx('|',new_val,"Modified",old_val);
run;
/* Transpose second time */
proc transpose data=compare_table_2 out=have2_new_trans_2;
by subject FolderSeq RecordPosition;
var new_val newval;
id variable_name;
run;
data temp1;
set have2_new_trans_2;
delim='|';
if scan(age,2,delim)="Modified" then flag='M';
else if scan(age,2,delim)="Added" then flag='A';
else if scan(age,2,delim)="Deleted" then flag='D';
run;
