DATA Step, Macro, Functions and more

Calculating length of interruptions between dates

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

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

 

Any advice is greatly appreciated.

Thank you very much in advance.


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 10,623

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;

View solution in original post


All Replies
Super User
Super User
Posts: 9,227

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
Super User
Posts: 9,227

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
2 weeks ago
Super User
Posts: 10,623

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

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;
		h.add(key:id,key:_month,data:suspension);
	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.

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

Discussion stats
  • 5 replies
  • 109 views
  • 2 likes
  • 4 in conversation