topic Re: Calculating length of interruptions between dates in SAS Programming

Calculating length of interruptions between dates

ciro — Mon, 14 May 2018 07:37:31 GMT

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.

Re: Calculating length of interruptions between dates

RW9 — Mon, 14 May 2018 07:57:40 GMT

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;

Re: Calculating length of interruptions between dates

ciro — Mon, 14 May 2018 12:30:36 GMT

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;

Re: Calculating length of interruptions between dates

RW9 — Mon, 14 May 2018 12:50:37 GMT

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.

Re: Calculating length of interruptions between dates

Ksharp — Mon, 14 May 2018 13:42:37 GMT

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;

Re: Calculating length of interruptions between dates

novinosrin — Mon, 14 May 2018 16:11:17 GMT

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;