Calculating length of interruptions between dates

[daszlosek]

Hello everyone,

I am trying to calculate the length and number of interruptions between dates for each subject ID. 

Here is the an example of what the data looks like in explorer:

usubjid	startdate	enddate
12	2009-03-25	2010-10-26
12	2010-10-30	2011-01-30
12	2011-02-15	2011-03-16
13	2010-12-11	2011-01-13
13	2011-04-13	2011-07-30

For each subjectid, I would like to know the total number of interruptions and the total length of each interruptions. 

Thanks for your help!

Donald S.

[mohamed_zaki]

Could you give example of your desired output based on the data you posted?

[daszlosek]

Yes Here is an example of the output, I also included how I would like to calculate the length:

usubjid	startdate	enddate	# of interruptions	Calc length	Length of Interruption
12	3/25/2009	10/26/2010
12	10/30/2010	1/30/2011	1	10/30/2010 - 10/26/2010	4
12	2/15/2011	3/16/2011	2	2/15/2011 - 1/30/2011	16
13	12/11/2010	1/13/2011
13	4/13/2011	7/30/2011	1	4/13/2011 - 1/13/2011	90

I hope this made things a little clearer,

Thank you,

Donald S.

[PGStats · Accepted solution]

Try this:

data have;
format startdate  enddate YYMMDD10.;
input usubjid startdate :YYMMDD10. enddate: YYMMDD10.;
datalines;
12	2009-03-25	2010-10-26
12	2010-10-30	2011-01-30
12	2011-02-15	2011-03-16
13	2010-12-11	2011-01-13
13	2011-04-13	2011-07-30
;

proc sort data = have;
by usubjid startdate;
run;

data want;
do until(last.usubjid);
    set have; by usubjid;
    call missing(daysInterruptions);
    if not missing(lastDate) then do;
        if startDate > lastDate then do;
            nbInterruptions = sum(nbInterruptions, 1);
            daysInterruptions = intck("DAY", lastDate, startDate);
            end;
        end;
    output;
    lastDate = endDate;
    end;
drop lastDate;
run; 

proc print data=want noobs; run;

[daszlosek]

Hello PGStats, your code worked exactly the way I needed! Thank you very much! I have never seen an until function before, but it makes things MUCH eaier than the do loops I was trying to do before! Glad to learn something new!

[FreelanceReinh]

Just a remark: There is no "UNTIL function", but only the DO UNTIL statement, which marks the beginning of a loop in which statements are executed repetitively until a condition is true. This exit condition is written in parentheses after the UNTIL keyword -- a notation which in fact resembles that of a function call.

PG used this DO UNTIL loop in a special way:

do until(last.varname);
  set dataset;
  by varname;
  ...
end;

This and similar constructs are known as "DOW loops." Here's an introductory paper on this useful, somewhat advanced SAS programming technique: http://www2.sas.com/proceedings/sugi28/099-28.pdf. There are more on the web, just search for "DOW loop" SAS.

[daszlosek]

Thank you FreelanceReinhard, I look forward to reading this!

[Jagadishkatam]

Please try this alternative method

data want;
set have;
by usubjid;
prevdate=lag(enddate);
if first.usubjid then prevdate=.;
Length_of_Interruption=intck('day',prevdate,startdate);
format prevdate date9.;
run;

[daszlosek]

Hello Jag,

Your way also works as a correct solution!

Thank you very much!

[Ksharp]

data have;
infile cards expandtabs truncover;
format startdate  enddate YYMMDD10.;
input usubjid startdate :YYMMDD10. enddate: YYMMDD10.;
datalines;
12	2009-03-25	2010-10-26
12	2010-10-30	2011-01-30
12	2011-02-15	2011-03-16
13	2010-12-11	2011-01-13
13	2011-04-13	2011-07-30
;

proc sort data = have;
by usubjid startdate;
run;
data want;
 set have;
 by usubjid ;
 lag_enddate=lag(enddate) ;
 if first.usubjid  then group=0;
  else if startdate gt lag_enddate then do;
    group+1;
    n=group;
    dif=startdate - lag_enddate;
  end;
 drop lag_enddate group;
run;