How to replace missing value

[Fangfang]

data have ;
input ID$ Month$ Treatment$ NEW_Treatment$ ;
cards;
1 Month_1 . Missing
1 Month_2 . Missing
1 Month_3 A A
1 Month_4 . Untrt
1 Month_5 B B
1 Month_6 . Untrt
1 Month_7 C C
1 Month_8 . Untrt
1 Month_9 . Untrt
1 Month_10 . Untrt
1 Month_11 E E
1 Month_12 . Untrt
2 Month_1 B B
2 Month_2 C C
2 Month_3 . Untrt
2 Month_4 D D
2 Month_5 . Untrt
2 Month_6 . Untrt
2 Month_7 . Untrt
2 Month_8 . Untrt
2 Month_9 E E
2 Month_10 . Untrt
2 Month_11 . Untrt
2 Month_12 A A
;
run;

Hi,

My current data looks like this， each ID has 12 months' Treatment (from Month_1 to Month_12). I want to replace the Treatment to NEW_treatment，namely, for each ID，the missing records before the first non-missing Treatment will be replaced as 'Missing', the missing records after the first non-missing Treatment will be replaced as 'Untrt'.

I am not sure how to do that. Any idea? Thanks!!

[singhsahab]

Hello Dear,

If i got your request correctly , here you can go ..

Data want;
set have;
if  missing (Treatment) then Treatment='Missing';
else Treatment='Untrt';
run;

otherwise please share a sample output for further answers..

[Fangfang]

Thank you for your help, you may misunderstand my request. 

[Astounding · Accepted solution]

This should take care of it.  It assumes your data is sorted:

data want;

set have;

by ID;

if first.ID then replacement = 'Missing';

if treatment > ' ' then replacement = 'Untrt';

retain replacement;

if treatment = ' ' then new_treatment = replacement;

else new_treatment = treatment;

drop replacement;

run;

[Fangfang]

Many thanks for your help. As I am a new SAS learner, I can not use your program flexibly; therefore, I have another question: For each ID, I want to replace the missing records before the first non-missing Treatment as 'Missing'; to replace missing records after the last non-missing Treatment as 'Discontinue'; to replace the missing records between the first non-missing Treatment and the last non-missing Treatment as 'Untrt'. Could you please give me a hand again? Thank you in advance.

[Astounding]

It can be done, but the programming becomes more complex.  You need to locate the last non-missing value.  As long as you are doing that, you might as well locate the first non-missing value as well.  For example:

data want;

ID_counter=0;

do until (last.id);

   set have;

   by ID;

   ID_counter + 1;

   if first_nonmissing = . and treatment > ' ' then first_nonmissing = ID_counter;

   if treatment > ' ' then last_nonmissing = ID_counter;

end;

ID_counter = 0;

do until (last.ID);

   set have;

   by ID;

   ID_counter + 1;

   if treatment = ' ' then do;

      if ID_counter < first_nonmissing then treatment = 'Missing';

      else if ID_counter < last_nonmissing then treatment = 'Discontinue';

      else treatment = 'Untrt';

   end;

   output;

end;

drop first_nonmissing last_nonmissing ID_counter;

run;

The top DO loop reads all the observations for an ID, numbers them, and captures where it finds the first and last non-missing values.  Then the second DO loops reads the exact same information and applies the logic for replacing TREATMENT.

It's  untested code, so I will look through a second time to see if I notice anything that needs to be corrected.

Note that if ALL the treatments are missing, the program will assign "Untrt" for that ID.

[Fangfang]

Many thanks for your reply and detail explanation. 

Based on your codes, I made some minor modifications to assign "Missing" for the ID with ALL the treatments missing.

Thank you again.

data want;
ID_counter=0;
do until (last.id);
   set have;
   by ID;
   ID_counter + 1;
   if first_nonmissing = . and Treatment > ' ' then first_nonmissing = ID_counter;
   if Treatment > ' ' then last_nonmissing = ID_counter;
end;

ID_counter = 0;
do until (last.ID);
   set have;
   by ID;
   ID_counter + 1;
   if Treatment = ' ' and first_nonmissing ^= . then do;
      if ID_counter < first_nonmissing then Want_treatment = 'Missing';
      else if ID_counter > last_nonmissing then Want_treatment = 'Discontinue';
      else Want_treatment = 'Untrt';
   end;
   if first_nonmissing = . then do ; Want_treatment = 'Missing';
   end;
   if Treatment ^= ' ' then do;
   	  Want_treatment = Treatment ;
   end ;
   output;
end;
run;