fill gap between records

[eric2]

Thanks all. All these worked but bit complicated code 😞

[novinosrin]

some more fun:

data one;
input id $3. visit;
datalines;
101 1
101 1
101 .
101 .
101 2
101 .
101 2
101 3
101 .
;
run;

data _null_;
if _n_=1 then do;
if 0 then set one;
 dcl hash h(multidata: 'y', ordered: 'a');
 h.definekey('_n_');
 h.definedata('id','visit');
 h.definedone();
end;
do _n_=nobs to 1 by -1;
set one point=_n_ nobs=nobs;
v=_n_;
if visit and not _visit  then _visit=visit;
else if  visit and  _visit and visit ne _visit then _visit=visit;
else if not visit and _visit then visit=_visit;
else if visit=_visit then call missing(_visit);
h.add();
output;
end;
h.output(dataset:'want');
stop;
run;

[mkeintz]

Perhaps this is less complicated.  Read all the records for a given id once to build an array (VIS) of visit values.  Each element of the array contains the visit number for the corresponding group of records.  Each group is a sequence of identical visit values.  For ID 101, there are seven groups with values {1, ., 2, ., 2, 3, .}. 

The reread (and output) the same records.  If a record has a value of . and is in group g, then check whether groups g-1 and g+1 have identical values.  If they do, then fillin in that value.

So if   group g has a visit value of . and groups g-1 and g+1 have matching values, then replace.

data want (drop=_:) ;

  array vis{0:20} _temporary_;

  do _g=1 by 1 until (last.id);
    do until (last.visit);
      set one;
      by id visit notsorted;
    end;
    vis{_g}=visit;
  end;
  _ng=_g;

  do _g=1 to _ng;
    if vis{_g}=. and vis{_g-1}=vis{_g+1} then _fillvis=vis{_g-1};
    else _fillvis=.;
    do until (last.visit);
      set one;
      by id visit notsorted;
      if visit=. then visit=_fillvis;
      output;
    end;
  end;
run;