Hello, I have updated code to add color and stick onto each column. but I have question yet.
Now the color only can add onto one column and then generate several reports. Can the color add onto all columns at one time and generate only one report? Any suggestion would be appreciated.
data test1;
input name & $12. age subject & $8. folderseq RecordPosition term & $48. rate ;
cards;
Frank 20 301001 1.0 1 Headache 5
Frank 20 301001 1.0 2 pain 5
Annie 31 301002 2.0 1 abc 4
Annie 31 301002 2.0 2 abcd 4
Jessica 38 301005 5.0 0 a1 3
;
run;
data test2;
input name & $12. age subject & $8. folderseq RecordPosition term & $48. rate ;
cards;
Frank 21 301001 1.0 1 headache 6
Frank 20 301001 1.0 2 pain 5
Annie 31 301002 2.0 1 abcd 4
Annie 31 301002 2.0 2 abcd 4
Andy 40 301004 4.0 0 b1 3
;
run;
data have1;
set test1;
run;
data have2;
set test2;
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);
else Newval=new_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;
%let compare_col_cnt=%sysfunc(countw(&compare_col));
%put &compare_col;
%put &compare_col_cnt;
%macro check(var);
data temp2;
set have2_new_trans_2;
delim='|';
if scan(&var,2,delim)="Modified" then flag='M';
else if scan(&var,2,delim)="Added" then flag='A';
else if scan(&var,2,delim)="Deleted" then flag='D';
run;
%mend check;
*%check(name);
*%check(rate);
%Macro eachvar(compare_col_cnt,compare_col);
%LET nummem = &compare_col_cnt;
%LET memlist = &compare_col;
%do i=1 %to &nummem;
%let memname=%scan(&memlist,&i);
%Check(&memname);
%end;
%MEND eachvar;
%eachvar(&compare_col_cnt,&compare_col);
data temp3;
set temp2;
where flag ^="";
run;
data temp4(drop=delim);
set temp3;
run;
%Macro addcolor;
%do i=1 %to &compare_col_cnt;
%let var1=%scan(&compare_col, &i);
proc report data=temp4;
column _all_;
define _col_/display;
compute &var1;
if scan(&var1,2,'|') = "Modified" then do;
flyover2="modified:"||kscan(&var1,3,'|');
call define(_col_,"style","style={background=LIGHTBLUE flyover='"||strip(flyover2)||"'}");
&var1 = kscan(&var1,1,'|');
end;
else if scan(&var1,2,'|') = "Added" then do;
flyover2="added:"||kscan(&var1,1,'|');
call define(_col_,"style","style={background=LIGHTGREEN flyover='"||strip(flyover2)||"'}");
&var1 = kscan(&var1,1,'|');
end;
else if scan(&var1,2,'|') = "Deleted" then do;
flyover2="deleted:"||kscan(&var1,3,'|');
call define(_col_,"style","style={background=LIGHTRED flyover='"||strip(flyover2)||"'}");
&var1 = "";
end;
endcomp;
quit;
%end;
%mend addcolor;
/* output excel file */
ods excel
file="/home/leo.zhang/sasuser.v94/STUDY_TEST/MML_Compare.xlsx";
%addcolor;
ods excel close;
Thanks and Best Regards Leo
Click image to register for webinar
