How to create new column with imputed values in reference to two other columns

[bretthouston]

I realize this is a very easy concept, but I'm having trouble assigning values to a new column when my logic involves 'if always' or 'if ever'. For example, in the following sample dataset:

ID            Status            UpdStatus

1                  yes                 yes

1                  yes                 yes

1                  no                   yes

2                  no                    no

3                  no                    no

4                 yes                  yes

4                 no                   yes

I am trying to create a new column, where for a given ID if status is ever 'yes' then UpdStatus = 'yes' otherwise UpdStatus = 'no'. [Alternatively, if status is always 'no' then UpdStatus = 'no' else 'yes']. I've written sample attempts below, but I can't figure out the proper language to convey the 'ever' or 'always'.

data want;

set have;

format UpdStatus $3.;

if Status="(ever) yes" then UpdStatus = "yes";

else UpdStatus = "no";

run;

or:

data want;

set have;

format UpdStatus $3.;

if Status ="(always) no" then UpdStatus="no";

else UpdStatus ="yes";

run;

Thanks in advance!

[novinosrin · Accepted solution]

Hi @bretthouston 

This assumes your dataset is sorted by ID as your sample suggests

data have;
input ID            Status       $;   
cards;
1                  yes                 yes
1                  yes                 yes
1                  no                   yes
2                  no                    no
3                  no                    no
4                 yes                  yes
4                 no                   yes
;

data want;
merge have have(in= b where=(status='yes'));
by id;
length updtd $3;
if b then updtd='yes';
else updtd='No';
run;

[bretthouston]

Thank-you - this is perfect!