topic Re: How to create new column with imputed values in reference to two other columns in New SAS User

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

bretthouston — Thu, 18 Apr 2019 15:08:39 GMT

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 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!

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

novinosrin — Thu, 18 Apr 2019 15:13:25 GMT

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;

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

bretthouston — Thu, 18 Apr 2019 15:30:20 GMT

Thank-you - this is perfect!