subtract to rows in the same column

Solved
Occasional Contributor
Posts: 12

subtract to rows in the same column

[ Edited ]

,

Accepted Solutions
Solution
‎06-12-2015 03:18 PM
Posts: 3,167

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/20151 2 rdate 05/21/20151 3 GAP 01 4 GAP 01 5 GAP 01 6 GAP 01 7 Sdate 07/05/20151 8 Sdate 07/09/20151 9 Sdate 07/11/20152 1 rdate 06/21/20152 2 GAP 02 3 Sdate 07/05/20152 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;

All Replies
Frequent Contributor
Posts: 146

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: 10,784

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/20151 2 rdate 05/21/20151 3 GAP 01 4 GAP 01 5 GAP 01 6 GAP 01 7 Sdate 07/05/20151 8 Sdate 07/09/20151 9 Sdate 07/11/20152 1 rdate 06/21/20152 2 GAP 02 3 Sdate 07/05/20152 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
Posts: 3,167

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/20151 2 rdate 05/21/20151 3 GAP 01 4 GAP 01 5 GAP 01 6 GAP 01 7 Sdate 07/05/20151 8 Sdate 07/09/20151 9 Sdate 07/11/20152 1 rdate 06/21/20152 2 GAP 02 3 Sdate 07/05/20152 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 and locked.