subtract to rows in the same column

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

subtract to rows in the same column

[ Edited ]

,


Accepted Solutions
Solution
‎06-12-2015 03:18 PM
Respected Advisor
Posts: 3,124

Re: subtract to rows in the same column

You have overcomplicated your issue in your description. From I can understand (please do correct me if I am wrong), what you want essentially is to fill up the gaps evenly with days between last rdate and next sdate. The following code uses 2XDOW and look-ahead technique, the first DOW is to obtain the interval size, the second DOW is to use this interval filling up the gap.

data have;

     input ID Email $ type $ edate mmddyy10.;

     format edate mmddyy10.;

     datalines;

1 1 rdate 05/20/2015

1 2 rdate 05/21/2015

1 3 GAP 0

1 4 GAP 0

1 5 GAP 0

1 6 GAP 0

1 7 Sdate 07/05/2015

1 8 Sdate 07/09/2015

1 9 Sdate 07/11/2015

2 1 rdate 06/21/2015

2 2 GAP 0

2 3 Sdate 07/05/2015

2 4 Sdate 07/09/2015

;

run;

data want;

     retain _start _invl;

     do _n_=1 by 1 until (last.type);

           set have;

           by id type notsorted;

           set have(firstobs=2 keep=edate rename=edate=_edate) have(obs=1 drop=_all_);

           if last.id then

                call missing(_edate);

           if last.type then

                do;

                     if  missing(_edate) then

                           _start=edate;

                     if type='GAP' then

                           _invl=round((_edate-_start)/(_n_+1));

                end;

     end;

     do _n_=1 by 1 until (last.type);

           set have;

           by id type notsorted;

           set have(firstobs=2 keep=edate rename=edate=_edate) have(obs=1 drop=_all_);

           if last.id then

                call missing(_edate);

           if missing(edate) then

                edate=sum(_start,_invl*_n_);

           output;

           if last.type and type='GAP' and edate>0 then

                call missing (_start,_invl);

     end;

     drop _:;

run;

View solution in original post


All Replies
Frequent Contributor
Posts: 144

Re: subtract to rows in the same column

Could exist more than one GAP per id?

Occasional Contributor
Posts: 12

Re: subtract to rows in the same column

Yes , I update the question

Super User
Posts: 9,681

Re: subtract to rows in the same column

Code: Program

data have;
input ID Email $ type $ edate ??  mmddyy10. ;
format edate mmddyy10.;
datalines;
1 1 rdate 05/20/2015
1 2 rdate 05/21/2015
1 3 GAP 0
1 4 GAP 0
1 5 GAP 0
1 6 GAP 0
1 7 Sdate 07/05/2015
1 8 Sdate 07/09/2015
1 9 Sdate 07/11/2015
2 1 rdate 06/21/2015
2 2 GAP 0
2 3 Sdate 07/05/2015
2 4 Sdate 07/09/2015
;
run;
data temp;
merge have have(keep=id type rename=(id=_id type =_type ) firstobs=2);
retain found;
if id=_id and type ='rdate' and _type ='GAP' then found=1;
if id=lag(id) and lag(type )='Sdate' and id=lag2(id) and lag2(type )='GAP' then found=.;
drop _:;
run;
data want;
n=0;
do until(last.found);
  set temp;
  by id found notsorted;
  n+1;
  if first.found and found=1 then s=edate ;
end;
  want=(edate-s)/(n-1);
do until(last.found);
  set temp;
  by id found notsorted;
  output;
end;
drop s n found;
run;
 
Solution
‎06-12-2015 03:18 PM
Respected Advisor
Posts: 3,124

Re: subtract to rows in the same column

You have overcomplicated your issue in your description. From I can understand (please do correct me if I am wrong), what you want essentially is to fill up the gaps evenly with days between last rdate and next sdate. The following code uses 2XDOW and look-ahead technique, the first DOW is to obtain the interval size, the second DOW is to use this interval filling up the gap.

data have;

     input ID Email $ type $ edate mmddyy10.;

     format edate mmddyy10.;

     datalines;

1 1 rdate 05/20/2015

1 2 rdate 05/21/2015

1 3 GAP 0

1 4 GAP 0

1 5 GAP 0

1 6 GAP 0

1 7 Sdate 07/05/2015

1 8 Sdate 07/09/2015

1 9 Sdate 07/11/2015

2 1 rdate 06/21/2015

2 2 GAP 0

2 3 Sdate 07/05/2015

2 4 Sdate 07/09/2015

;

run;

data want;

     retain _start _invl;

     do _n_=1 by 1 until (last.type);

           set have;

           by id type notsorted;

           set have(firstobs=2 keep=edate rename=edate=_edate) have(obs=1 drop=_all_);

           if last.id then

                call missing(_edate);

           if last.type then

                do;

                     if  missing(_edate) then

                           _start=edate;

                     if type='GAP' then

                           _invl=round((_edate-_start)/(_n_+1));

                end;

     end;

     do _n_=1 by 1 until (last.type);

           set have;

           by id type notsorted;

           set have(firstobs=2 keep=edate rename=edate=_edate) have(obs=1 drop=_all_);

           if last.id then

                call missing(_edate);

           if missing(edate) then

                edate=sum(_start,_invl*_n_);

           output;

           if last.type and type='GAP' and edate>0 then

                call missing (_start,_invl);

     end;

     drop _:;

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 507 views
  • 0 likes
  • 4 in conversation