topic Re: fill gap between records in SAS Programming

fill gap between records

eric2 — Fri, 06 Apr 2018 15:15:00 GMT

Hi,

how do i fill gaps between records. since there is gap between visit 1 and visit 2 i dont want those gaps to be filled but i want gaps filled between same visit like there is gap between visit 2, so that gap should be filled with same visit thats 2.

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

final output expected should be

101 1
101 1
101 .
101 .
101 2
101 2
101 2
101 3

thanks,

Re: fill gap between records

SuryaKiran — Fri, 06 Apr 2018 16:02:18 GMT

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


data want;
retain _Visit;
do i=1 by 1 until(not missing(Visit_));
	set one(rename=Visit=Visit_);
	end;
do j=1 to i;
set one;
If not missing(visit) then _Visit=Visit;
if _Visit=Visit_ then Visit=_Visit;
Output;
end;
keep id visit;
run;

I need to thank @PGStats , I learned this approach from him

Re: fill gap between records

eric2 — Fri, 06 Apr 2018 16:48:38 GMT

Thanks. Good one but if your last record is has missing visit then your total number of records are decreased by one and we want to have same number of records as original one.

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

thx

Re: fill gap between records

novinosrin — Fri, 06 Apr 2018 17:18:23 GMT

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

data _null_;
if _n_=1 then do;
dcl hash H (multidata:'y',ordered:'a') ;
   h.definekey  ("id",'_visit') ;
   h.definedata ('id',"visit") ;
   h.definedone () ;
end;
set one end=last;
by id ;
retain _visit;
if not missing(visit) and lag(visit)=. and visit=_visit then 
h.replace(key:id,key:_visit, data:id,data: _visit);
if not missing(visit) then _visit=visit;
h.add();
if last then h.output(dataset:'want');
run;

Re: fill gap between records

eric2 — Fri, 06 Apr 2018 17:29:37 GMT

Thanks . I am not so familiar with hash objects. How do you keep other variables if you have say more variables in this dataset but dont use them in your logic?

thx

Re: fill gap between records

novinosrin — Fri, 06 Apr 2018 17:34:00 GMT

specify all of them in the definedata section as you initialize the hash obj at the top

  h.definedata ('id',"visit") ;/*and others to be included */

but i think this will require the data: in replace method to be expanded too making it a verbose

Re: fill gap between records

eric2 — Fri, 06 Apr 2018 17:39:12 GMT

Thanks . I will try it out but i wish there is a way to do it by not using hash objects.

Re: fill gap between records

novinosrin — Fri, 06 Apr 2018 18:03:59 GMT

@eric2 Ok, an easy solution for you and not to worry about variables at all:

data one;
input id $3. visit;
datalines;
101 1
101 1
101 .
101 .
101 2
101 .
101 2
101 3
101 .
;
run;
data want;
do _n_=1 by 1 until(last.id);
set one;
by id  ;
array _t(10);/*Please assign a high value subscript as mine is arbitrary/small for demo*/
if not missing(visit) and lag(visit)=. and visit=_visit then _t(_n_)=visit;
if not missing(visit) then _visit=visit;
end;
do until(last.id);
set one;
by id;
if not missing(visit) then _visit=visit;
if _visit  in _t then  visit=_visit;
output;
end;
drop _:;
run;

Re: fill gap between records

eric2 — Fri, 06 Apr 2018 18:20:29 GMT

awesome. thanks much. this worked ....: smileyhappy:

Re: fill gap between records

PGStats — Fri, 06 Apr 2018 22:12:46 GMT

Covering all cases:

data one;
input id $3. visit;
datalines;
101 1
101 1
101 .
101 .
101 2
101 .
101 2
101 3
102 .
102 3
102 .
102 3
103 4
103 5
103 .
;

data want;
do until(last.id);
    do i = 1 by 1 until(last.id or not missing(Visit_));
    	set one(rename=Visit=Visit_); by id;
    	end;
    do j = 1 to i;
        set one;
        If missing(visit) then if _Visit = Visit_ then Visit = _Visit;
        Output;
        end;
    _Visit = Visit_;
    end;
keep id visit;
run;

proc print; run;

Re: fill gap between records

novinosrin — Fri, 06 Apr 2018 23:37:08 GMT

@PGStats Sir, may i request a min or two for a quick clarification on your very interesting statement that i want to learn and comprehend

If missing(visit) then if _Visit = Visit_ then Visit = _Visit;

how does the above evaluate if then follows if then

Did i miss something not in doc http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000202239.htm

I would appreciate your response and time. Thank you

EDITED at 6:35 chicago time after figuring out with some diligent reading:

I got the understanding. My bad I didn't realize "if then" is executable and is used in one if then without an if then do. Sorry for the bother

Re: fill gap between records

mkeintz — Sat, 07 Apr 2018 04:23:18 GMT

This program uses the lag function, which updates lagged value of visit only once per visit "group", i.e. only at the end of a sequence of identical visit values. If the lagged value is missing, and the double-lagged value matches the current value then set _FILLIN=1.

Reread the records read above, and if _FILLIN=1 then replace missing visit values with the double-lagged value. After the re-read output the records:

data want (drop=_:);
  do _N=1 by 1 until (last.id or _visit_lag1=.);
    set one;
    by id visit notsorted;
    if last.visit then do;
      _visit_lag1=lag1(visit);
      _visit_lag2=lag2(visit);
      _fillin= (visit=_visit_lag2);
    end;
  end;
  do _I=1 to _N;
    set one;
    if visit=. and _fillin=1 then visit=_visit_lag2;
    output;
  end;
run;

Re: fill gap between records

Ksharp — Sat, 07 Apr 2018 11:58:36 GMT

data one;
input id visit;
datalines;
101 1
101 1
101 .
101 .
101 2
101 .
101 2
101 3
101 .
1011 1
1011 1
1011 .
1011 .
1011 2
1011 .
1011 2
1011 3
1011 .
;
run;
data have;
 set one;
 by id;
 if first.id then n=0;
 n+1;
run;
data temp;
 set have;
 if not missing(visit);
run;
data temp1;
 merge temp temp(rename=(id=_id n=_n visit=_visit) firstobs=2);
 output;
 if id=_id then do;
  do i=n+1 to _n-1;
    if visit ne _visit then call missing(visit);
	n=i;
	output;
  end;
 end;
 drop _: i ;
run;
data want;
 merge have temp1(rename=(visit=_visit) in=inb) ;
 by id n;
 if inb then visit=_visit;
 drop _visit;
run;

Re: fill gap between records

Ksharp — Sat, 07 Apr 2018 13:32:58 GMT

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

data temp;
 retain _visit;
 do until(last.id or not missing(visit));
   set one;
   by id;
   if first.id then call missing(_visit);
 end;
 flag=(_visit=visit);
 do until(last.id or not missing(visit));
   set one;
   by id;
   if flag then visit1=_visit;
   output;
 end;
 _visit=visit;
 drop flag _visit;
 run;
 data want;
  set temp;
  if not missing(visit1) then visit=visit1;
  drop visit1;
run;

Re: fill gap between records

s_lassen — Mon, 09 Apr 2018 11:40:33 GMT

Here is a solution which updates the data in place:

data temp;
  set one;
  if not missing(visit);
  obsno=_N_;
  rename visit=replace_val;
run;

data one;
  set temp;
  by id replace_val notsorted;
  last_n=lag(obsno);
  if not first.replace_val then do _N_=last_n+1 to obsno-1;
    modify one point=_N_;
    visit=replace_val;
    replace;
    end;
run;

Re: fill gap between records

eric2 — Mon, 09 Apr 2018 16:23:22 GMT

Thanks all. All these worked but bit complicated code 😞

Re: fill gap between records

novinosrin — Mon, 09 Apr 2018 16:53:07 GMT

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;

Re: fill gap between records

mkeintz — Tue, 10 Apr 2018 21:08:15 GMT

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;