Update an Index variable based of the values it takes

Question

Suppose I have the following dataset

data DB;
  input ID :$20. morbidity;
cards;
0001   1 
0001   1
0001   0   
0001   2  
0002   2 
0002   0
0002   2;
run;

Is there a way to get the following?

data DB1;
  input ID :$20. morbidity;
cards;
0001   1 
0001   1
0001   0   
0001   0  
0002   2 
0002   0
0002   2;
run;

In other words, if the patient has at least one time morbidity = 1 all the remaining times morbidity = 2 it should become 0.

Answer 1

An easy way to do this would to be with a hash table lookup for all IDs that have a morbidity of 1. If the value of an ID in the table matches the value of an ID in the hash table and the morbidity value is 2, then set it to 0. In other words, if we have any match to an ID in this lookup table which only contains IDs with at least one value of morbidity = 1:

ID
0001

Then we know that morbidity = 1 at some point for the ID in the set table we're reading. Since we have found that match, we can check if the value of morbidity is 2. If it is, set it to 0.

data want;
    set db;

    if(_N_ = 1) then do;
        dcl hash lookup(dataset: 'db(where=(morbidity=1))');
            lookup.defineKey('id');
        lookup.defineDone();
    end;

    if(lookup.Check() = 0 AND morbidity = 2)
        then morbidity = 0;
run;

ID      morbidity
0001    1
0001    1
0001    0
0001    0
0002    2
0002    0
0002    2