@leahcho: Yes, it can be done, just with a bit more programming fuss than using SQL: data have ;
input ID A B C ;
cards ;
1 1 0 0
1 0 1 0
1 1 1 0
2 0 1 1
2 1 0 1
run ;
data want_by (drop = _:) ;
do until (last.ID) ;
set have ;
by ID ;
array vv A B C ;
array mm _m1-_m3 ;
do over vv ;
mm = mm <> vv ;
end ;
end ;
do over vv ;
vv = mm ;
end ;
run ; However, as @Kurt_Bremser has pointed out, it requires HAVE to be sorted or grouped by ID. If it's not sorted, it still can be done but needs a hash table to accumulate the maxima - and still more programming fuss: data _null_ ;
dcl hash max (hashexp:0) ;
max.definekey ("ID") ;
max.definedata ("ID", "A", "B", "C") ;
max.definedone () ;
do until (last) ;
set have (rename=(A=m1 B=m2 C=m3)) end = last
array vv A B C ;
array mm m1 - m3 ;
rc = max.find() ;
do over vv ;
if rc ne 0 then vv = mm ;
else vv = mm <> vv ;
end ;
max.replace() ;
end ;
max.output (dataset:"want") ;
run ; Note that HASHEXP:0 directs the hash object to use a single (2**0=1) AVL tree. It has two effects: (1) Keeps the table's base structure leaner (though it results in a slower search - but it's not a concern here). (2) Results in the table being ordered by ID without coding the ORDERED:"A" argument tag. This is because with a single tree, the hash function has nothing else to point at, and the keys in an AVL tree are always in order. HTH Paul D.
... View more