hi Dear all: thank you so much,👍 maybe I didn't explain the goal clearly. I had first checked SASpaper . this is the exactly what I need to accomplish, there are many places need to be fixed though. I will try to use this as blueprint.    https://www.pharmasug.org/proceedings/2017/AD/PharmaSUG-2017-AD04.pdf Have a nice day. P ... View more

thanks for all the valuable input. there is one solution I found: when there is many to many merge, we need to find out or derive a unique key for a by var. , I finally found that Unique key var. By doing us, we may be able to Convert a Many to Many as a One to One. eg. /*RAW DATA*/ data class; set sashelp.class end=eof; if name='Jane' then do ; weight=150; end; if name='Carol' then do; age=19; end; output; if eof then do; name = "Meghan"; sex = "F"; age = 15; output; name = "Joe"; height = 55 ; weight = 88; output; name = "Meghan"; sex = "F"; age = 15; output; name = "Jane"; sex = "F"; height=44; age = 15; output; name = "John"; sex = "F"; age = 12; weight=99; output; end; run; proc sort;by name age;run; data classf; set class; retain unique; by name; unique+1; if first.name then unique=1; run; proc sort;by name unique;run; data class2; set sashelp.class end=eof; if name="Mary" then delete; if name="Alice" then delete; output; if eof then do; name = "Meghan"; sex = "F"; weight=90 ; age = 16; output; name = "Meghan"; sex = "F"; age = 15; output; name = "Joe"; height = 55 ; weight = 88; output; name = "Meghan"; sex = "F"; age = 15; output; name = "Jane"; sex = "F"; height=44; age = 15; output; name = "John"; sex = "F"; age = 12; weight=110; output; name = "John"; sex = "F"; age = 12; height=69; weight=77; OUTPUT; name = "Frank"; sex = "M"; age = 19; height=60; weight=70; output; name="Sara"; sex="F"; age=16; height=57; weight=79; output; end; run; data classnew; set class2; new_age =age; new_height=height; new_sex=sex; new_weight =weight; drop age height sex weight; run; proc sort;by name ;run; data classnewf; set classnew; retain unique; by name; unique+1; if first.name then unique=1; run; proc sort;by name unique;run; /*NEED TO APPEND BOTH CLASS AND CLASSNEW TO GET _CHECK= "NEW" "DELETED" "REVISED"*/ data result; merge classf (in=a) classnewf(in=b ); by name unique; if a and not b then _ck="Deleted"; if b and not a then _ck="New"; if (a and b) then _ck="Both"; run; data final; set result; if _ck in ("New","Deleted") then _ck2=_ck; if _ck ="Both" then do; if (new_age~=age or new_sex~=sex or new_height~=height or new_weight~=weight) then _ck2="Revised" ; end; run; /**/   ... View more

Hi Tom: thanks for reminder, I will need to learn before use the macro. currently need to solve the issue for job. ... View more