Thanks all. All these worked but bit complicated code 😞
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;
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.