BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
daszlosek
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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;
PG

View solution in original post

9 REPLIES 9
mohamed_zaki
Barite | Level 11

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

daszlosek
Quartz | Level 8

 

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
Opal | Level 21

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;
PG
daszlosek
Quartz | Level 8

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
Jade | Level 19

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
Quartz | Level 8
Thank you FreelanceReinhard, I look forward to reading this!
Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag
daszlosek
Quartz | Level 8
Hello Jag,

Your way also works as a correct solution!

Thank you very much!
Ksharp
Super User
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;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1369 views
  • 5 likes
  • 6 in conversation