Solved
Contributor
Posts: 66

# Calculating length of interruptions between dates

hello,

I have subjects over several months (up to 12). sometime there is an ending date that may signal the end of treatment or just a suspension. in the first case in the months ahead I do not find a (new) beginning date, while in the second case I find one.

I would like to flag the months that are a suspension.

here is an example. dataset have:

id month date_beg date_end
1 1 . .
1 2 . .
1 3 . 13MAR2017
1 4 . .
1 5 . .
1 6 30JUN2017 .
1 7 . .
1 8 . .
1 9 . .
1 10 . .
1 11 . .
1 12 . .
2 1 . .
2 2 . .
2 3 . 13MAR2017
2 4 . .
2 5 . .
2 6 . .
2 7 . .
2 8 . .
2 9 . .
2 10 . .
2 11 . .
2 12 . .
3 1 . .
3 2 . .
3 3 . .
3 4 . .
3 5 . .
3 6 30JUN2017 .
3 7 . .
3 8 . .
3 9 . .
3 10 . .
3 11 . .
3 12 . .
4 1 . .
4 2 . 20FEB2017
4 3 . .
4 4 15APR2017 .
4 5 . 10MAY2017
4 6 20JUN2017 .
4 7 . .
4 8 . 02AUG2017
4 9 . .
4 10 20OCT2017 .
4 11 . .
4 12 . .

dataset want:

id month date_beg date_end suspension
1 1 . . 0
1 2 . . 0
1 3 . 13MAR2017 0
1 4 . . 1
1 5 . . 1
1 6 30JUN2017 . 0
1 7 . . 0
1 8 . . 0
1 9 . . 0
1 10 . . 0
1 11 . . 0
1 12 . . 0
2 1 . . 0
2 2 . . 0
2 3 . 13MAR2017 0
2 4 . . 0
2 5 . . 0
2 6 . . 0
2 7 . . 0
2 8 . . 0
2 9 . . 0
2 10 . . 0
2 11 . . 0
2 12 . . 0
3 1 . . 0
3 2 . . 0
3 3 . . 0
3 4 . . 0
3 5 . . 0
3 6 30JUN2017 . 0
3 7 . . 0
3 8 . . 0
3 9 . . 0
3 10 . . 0
3 11 . . 0
3 12 . . 0
4 1 . . 0
4 2 . 20FEB2017 0
4 3 . . 1
4 4 15APR2017 . 0
4 5 . 10MAY2017 0
4 6 20JUN2017 . 0
4 7 . . 0
4 8 . 02AUG2017 0
4 9 . . 1
4 10 20OCT2017 . 0
4 11 . . 0
4 12 . . 0

Thank you very much in advance.

Accepted Solutions
Solution
‎05-15-2018 05:51 AM
Super User
Posts: 10,860

## Re: Calculating length of interruptions between dates

If I understood what you mean.

``````data have;
input id	month	date_beg : date9.	date_end  : date9.;
format date_beg 	date_end   date9.;
cards;
1	1	.	.
1	2	.	.
1	3	.	13MAR2017
1	4	.	.
1	5	.	.
1	6	30JUN2017	.
1	7	.	.
1	8	.	.
1	9	.	.
1	10	.	.
1	11	.	.
1	12	.	.
2	1	.	.
2	2	.	.
2	3	.	13MAR2017
2	4	.	.
2	5	.	.
2	6	.	.
2	7	.	.
2	8	.	.
2	9	.	.
2	10	.	.
2	11	.	.
2	12	.	.
3	1	.	.
3	2	.	.
3	3	.	.
3	4	.	.
3	5	.	.
3	6	30JUN2017	.
3	7	.	.
3	8	.	.
3	9	.	.
3	10	.	.
3	11	.	.
3	12	.	.
4	1	.	.
4	2	.	20FEB2017
4	3	.	.
4	4	15APR2017	.
4	5	.	10MAY2017
4	6	20JUN2017	.
4	7	.	.
4	8	.	02AUG2017
4	9	.	.
4	10	20OCT2017	.
4	11	.	.
4	12	.	.
;
run;
data temp;
set have;
if n(date_beg ,date_end );
flag=not missing(date_end);
run;
data id;
set temp;
by id;
if first.id and flag then output;
run;
proc sql;
create table temp1 as
select *
from temp
where id in (select id from id)
group by id
having count(*) ne 1
order by id,month;
quit;
data temp1;
set temp1;
if flag then group+1;
run;
data temp2;
merge temp1 temp1(firstobs=2 keep=group month rename=(group=_group month=_month));
output;
if group=_group then do;
do i=month+1 to _month-1;
month=i;suspension=1;output;
end;
end;
keep id month suspension;
run;

proc sort data=temp2;by id month;run;
data want;
merge have temp2;
by id month;
run;``````

All Replies
Super User
Posts: 9,866

## Re: Calculating length of interruptions between dates

[ Edited ]

Post test data in the form of a datastep.  As such this code is untested, but retain should work:

```data want;
set have;
retain suspension 0;
if date_end ne . then do;
output;
suspension=1;
end;
else if date_beg ne . then do;
suspension=0;
output;
end;
else output;
run;
```
Contributor
Posts: 66

## Re: Calculating length of interruptions between dates

apologies for not having posted the datastep. I do it now at the end of the message.

thank you for the suggestion.

unfortunately the code posted signal as interruption also a case where the ending date is not followed, in any of the next months, by a beginiing date.

any other idea?

data have;
format date_end date9.;
format date_beg date9.;
do id=1 to 4;
do month=1 to 12;
if id=1 then do;
if month=3 then date_end='13mar2017'd;
else if month=6 then date_beg='30jun2017'd;
else do;
date_end=.;
date_beg=.;
end;
end;

else if id=2 then do;
if month=3 then date_end='13mar2017'd;
else do;
date_end=.;
date_beg=.;
end;
end;

else if id=3 then do;
if month=6 then date_beg='30jun2017'd;
else do;
date_end=.;
date_beg=.;
end;
end;
else if id=4 then do;
if month=2 then date_end='20feb2017'd;
else if month=4 then date_beg='15apr2017'd;
else if month=5 then do; date_beg=.; date_end='10may2017'd; end;
else if month=6 then do; date_beg='20jun2017'd; date_end=.; end;
else if month=8 then date_end='2aug2017'd;
else if month=10 then date_beg='20oct2017'd;
else do;
date_end=.;
date_beg=.;
end;
end;

output;
end;
end;
run;

Super User
Posts: 9,866

## Re: Calculating length of interruptions between dates

Sorry, I do not understand what you mean.  Post test data in the form of a datastep which shows what you have, and then show the output you expect from that test data.  From the data you posted, what I suggest should work, if not, show the data where this doesn't.

Solution
‎05-15-2018 05:51 AM
Super User
Posts: 10,860

## Re: Calculating length of interruptions between dates

If I understood what you mean.

``````data have;
input id	month	date_beg : date9.	date_end  : date9.;
format date_beg 	date_end   date9.;
cards;
1	1	.	.
1	2	.	.
1	3	.	13MAR2017
1	4	.	.
1	5	.	.
1	6	30JUN2017	.
1	7	.	.
1	8	.	.
1	9	.	.
1	10	.	.
1	11	.	.
1	12	.	.
2	1	.	.
2	2	.	.
2	3	.	13MAR2017
2	4	.	.
2	5	.	.
2	6	.	.
2	7	.	.
2	8	.	.
2	9	.	.
2	10	.	.
2	11	.	.
2	12	.	.
3	1	.	.
3	2	.	.
3	3	.	.
3	4	.	.
3	5	.	.
3	6	30JUN2017	.
3	7	.	.
3	8	.	.
3	9	.	.
3	10	.	.
3	11	.	.
3	12	.	.
4	1	.	.
4	2	.	20FEB2017
4	3	.	.
4	4	15APR2017	.
4	5	.	10MAY2017
4	6	20JUN2017	.
4	7	.	.
4	8	.	02AUG2017
4	9	.	.
4	10	20OCT2017	.
4	11	.	.
4	12	.	.
;
run;
data temp;
set have;
if n(date_beg ,date_end );
flag=not missing(date_end);
run;
data id;
set temp;
by id;
if first.id and flag then output;
run;
proc sql;
create table temp1 as
select *
from temp
where id in (select id from id)
group by id
having count(*) ne 1
order by id,month;
quit;
data temp1;
set temp1;
if flag then group+1;
run;
data temp2;
merge temp1 temp1(firstobs=2 keep=group month rename=(group=_group month=_month));
output;
if group=_group then do;
do i=month+1 to _month-1;
month=i;suspension=1;output;
end;
end;
keep id month suspension;
run;

proc sort data=temp2;by id month;run;
data want;
merge have temp2;
by id month;
run;``````
Super User
Posts: 2,078

## Re: Calculating length of interruptions between dates

``````data have;
input id	month	date_beg : date9.	date_end  : date9.;
format date_beg 	date_end   date9.;
cards;
1	1	.	.
1	2	.	.
1	3	.	13MAR2017
1	4	.	.
1	5	.	.
1	6	30JUN2017	.
1	7	.	.
1	8	.	.
1	9	.	.
1	10	.	.
1	11	.	.
1	12	.	.
2	1	.	.
2	2	.	.
2	3	.	13MAR2017
2	4	.	.
2	5	.	.
2	6	.	.
2	7	.	.
2	8	.	.
2	9	.	.
2	10	.	.
2	11	.	.
2	12	.	.
3	1	.	.
3	2	.	.
3	3	.	.
3	4	.	.
3	5	.	.
3	6	30JUN2017	.
3	7	.	.
3	8	.	.
3	9	.	.
3	10	.	.
3	11	.	.
3	12	.	.
4	1	.	.
4	2	.	20FEB2017
4	3	.	.
4	4	15APR2017	.
4	5	.	10MAY2017
4	6	20JUN2017	.
4	7	.	.
4	8	.	02AUG2017
4	9	.	.
4	10	20OCT2017	.
4	11	.	.
4	12	.	.
;
run;

data want;
if _n_=1 then do;
if 0 then set have;
dcl hash H (ordered: "A") ;
h.definekey  ("id",'month') ;
h.definedata ('suspension') ;
h.definedone () ;
call missing(suspension);
end;
do  until(last.id);
set have end=last;
by id month;
if missing(date_beg) and date_end then _k=month;
else if date_beg and missing(date_end) and _k then do;
do _month=_k+1 to month-1;
suspension=1;
end;
end;
end;
do until(last.id);
set have;
by id month;
suspension=0;
rc=h.find();
output;
end;
drop _: rc;
run;``````
☑ This topic is solved.