Filling in missing values over time | SAS Code Fragments

Say that you had data over time where the values were stable over time, but you had gaps of missing data.  Say that you were comfortable filling in "gaps" as long as they were straddled by valid data with the same values on both sides. So if a person had values of 

"missing" "1" "missing" "missing" "1" "missing"

you felt comfortable treating them as 

"missing "1" "1" "1" "1" "missing"

Here is an example of how you can fill in the missing values that are straddled by the same values.

data test;
  input id visit yesno;
cards;
1 1 .
1 2 .
1 3 1
1 4 .
1 5 .
1 6 .
1 7 1
1 8 .
2 1 1
2 2 .
2 3 0
2 4 .
3 1 .
3 2 0
3 3 .
3 4 .
3 5 1
4 1 .
4 2 .
4 3 0
4 4 .
4 5 .
4 6 .
4 7 0
4 8 .
5 1 1
5 2 .
5 3 1
;
run;

proc sort data=test;
  by id visit;
run;

data test2;
  set test;
  by id;
  if first.id then yesnoup = .;
  if yesno ne . then yesnoup = yesno;
  retain yesnoup;
run;

proc print data=test2;
run;

proc sort data=test2;
  by id  descending visit ;
run;

data test3;
  set test2;
  by id;
  if first.id then yesnodn = .;
  if yesno ne . then yesnodn = yesno;
  retain yesnodn;
run;

proc print data=test3;
run;

proc sort data=test3;
  by id visit;
run;

data test4;
  set test3;
  if (yesnoup eq yesnodn) and (yesno eq .) then fillval = yesnoup;
run;

proc print data=test4;
  var id visit yesno fillval yesnoup yesnodn;
run;

---

Now, say that instead of just having one variable, you had two (or more) variables.  Here is how you can do the same thing, but using an array.

data test;
  input id visit yesno1 yesno2;
cards;
1 1 . 0 
1 2 . 0
1 3 1 .
1 4 . 1
1 5 . .
1 6 . 1
1 7 1 1
1 8 . 1
2 1 1 .
2 2 . 0
2 3 0 1
2 4 . .
3 1 . 1
3 2 0 .
3 3 . 0
3 4 . 0
3 5 1 .
4 1 . 1
4 2 . .
4 3 0 1
4 4 . 0
4 5 . 1
4 6 . 0
4 7 0 1
4 8 . 1
5 1 1 1
5 2 . 1
5 3 1 1
;
run;

proc sort data=test;
  by id visit;
run;

data test2;
  set test;
  by id;
  array Ayesno(2) yesno1-yesno2;
  array Ayesnoup(2) yesnoup1-yesnoup2;
  retain yesnoup1-yesnoup2;
  if first.id then do;
    do i = 1 to 2;
      Ayesnoup(i) = .;
	end;
  end;
  do i = 1 to 2;
    If Ayesno(i) ne . then Ayesnoup(i) = Ayesno(i);
  end;
run;

proc print data=test2;
run;

proc sort data=test2;
  by id  descending visit ;
run;

data test3;
  set test2;
  by id;
  array Ayesno(2) yesno1-yesno2;
  array Ayesnodn(2) yesnodn1-yesnodn2;
  retain yesnodn1-yesnodn2;
  if first.id then do;
    do i = 1 to 2;
      Ayesnodn(i) = .;
	end;
  end;
  do i = 1 to 2;
    If Ayesno(i) ne . then Ayesnodn(i) = Ayesno(i);
  end;
run;

proc print data=test3;
run;

proc sort data=test3;
  by id visit;
run;

data test4;
  set test3;
  array Ayesno(2) yesno1-yesno2;
  array Ayesnodn(2) yesnodn1-yesnodn2;
  array Ayesnoup(2) yesnoup1-yesnoup2;
  array ANewYesNo(2) NewYesNo1-NewYesNo2;
  do i = 1 to 2;
    if (Ayesnoup(i) eq Ayesnodn(i)) and (Ayesno(i) eq .) then ANewYesNo(i) = Ayesnoup(i);
	else
    ANewYesNo(i) = Ayesno(i);
  end;
run;

proc print data=test4;
  var id visit yesno1 yesno2 newyesno1 newyesno2;
run;