fill gap between records

Reply
Occasional Contributor
Posts: 19

fill gap between records

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,

er

Valued Guide
Posts: 597

Re: fill gap between records

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 Smiley Embarassed

 

Thanks,
Suryakiran
Occasional Contributor
Posts: 19

Re: fill gap between records

Posted in reply to SuryaKiran

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

Trusted Advisor
Posts: 1,345

Re: fill gap between records

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;
PROC Star
Posts: 1,836

Re: fill gap between records

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;
Occasional Contributor
Posts: 19

Re: fill gap between records

Posted in reply to novinosrin

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

PROC Star
Posts: 1,836

Re: fill gap between records

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 

Occasional Contributor
Posts: 19

Re: fill gap between records

Posted in reply to novinosrin

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

PROC Star
Posts: 1,836

Re: fill gap between records

[ Edited ]

@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;

 

Occasional Contributor
Posts: 19

Re: fill gap between records

Posted in reply to novinosrin

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

Esteemed Advisor
Posts: 5,541

Re: fill gap between records

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;
PG
PROC Star
Posts: 1,836

Re: fill gap between records

[ Edited ]

@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

Super User
Posts: 10,787

Re: fill gap between records

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;
Super User
Posts: 10,787

Re: fill gap between records

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;
PROC Star
Posts: 266

Re: fill gap between records

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;

 

 

Ask a Question
Discussion stats
  • 17 replies
  • 263 views
  • 2 likes
  • 7 in conversation