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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.