Day intervals

[uabcms]

Need some help with summing day intervals.  I've got a file with an ID and an admit and discharge date.

Within each ID group I want to get the get the number of days from the discharge date (ddate) of  first vist to the admit date (adate) of the

current record.   But, if the interval reaches 91 days I need to start the count over again.  (stated another way, I want to

create 90-day groups within an ID.)

Ex:   

ID         adate1           ddate2         interval (what I want)         group  (what I want)

1          9/28/13          10/7/13              0                                             1   

2          9/20/13          9/29/13              0                                             1

3          1/4/13           1/22/13               0                                             1

3          2/24/13         2/28/13               33                                           1

4          2/4/13           2/5/13                 0                                             1

4          2/13/13         2/18/13               8                                             1

4          4/24/13         4/27/13               68                                           1              

4          6/3/13          6/10/13              108                                           2

5          8/18/13         8/22/13               0                                             1

5          12/1/13         12/12/13            101                                          2

5          12/14/13       12/22/13             114                                         2 

6          6/7/13          6/14/13                 0                                             1

6          6/14/13          6/26/13               0                                             1

6          7/3/13          7/19/13                 19                                           1

6          9/5/13          9/9/13                  83                                            1 

6          9/15/13     10/15/13                 93                                            2

Hope this is clear.     Thanks.

[Haikuo]

data have;

input ID         (adate1           ddate2) (:mmddyy8.) ;

format adate1 ddate2 mmddyy10.;

cards;

1          9/28/13          10/7/13      

2          9/20/13          9/29/13      

3          1/4/13           1/22/13      

3          2/24/13         2/28/13       

4          2/4/13           2/5/13       

4          2/13/13         2/18/13       

4          4/24/13         4/27/13       

4          6/3/13          6/10/13       

5          8/18/13         8/22/13       

5          12/1/13         12/12/13      

5          12/14/13       12/22/13       

6          6/7/13          6/14/13       

6          6/14/13          6/26/13      

6          7/3/13          7/19/13       

6          9/5/13          9/9/13        

6          9/15/13     10/15/13          

;

data want;

set have;

by id;

     retain _dd interval group;

       if first.id then do; _dd=ddate2; group=1;end;

       if first.id then interval=0; else interval=intck('day', _dd,adate1);

       if interval >= 91 then do; group+1; _dd=ddate2;end;

     drop _dd;

run;

Good Luck!

Haikuo

[RW9]

Something along the lines of:

proc sql;

  create table WORK.DATES2 as

  select  CURRENT.ID,

          CURRENT.ADATE,

          CURRENT.DDATE,

          datdif(BASE.ADATE,CURRENT.DDATE,"ACT/ACT") as INTERVAL,

          round(CALCULATED INTERVAL / 91,1) +1 as GRP

  from    WORK.DATES CURRENT

  left join (select ID,MIN(ADATE) as ADATE from WORK.DATES group by ID) BASE

  on      CURRENT.ID=BASE.ID;

quit;

[Haikuo]

"But, if the interval reaches 91 days I need to start the count over again"

the way I interpret this sentence is that the start point of counting needs to be reset once a new group is forming. If that is indeed the case (well, OP has the final call), I doubt Proc SQL could offer a solid approach.

Haikuo

[RW9]

Hi,

Maybe, but looking at the wanted data:

4          2/4/13           2/5/13                 0                                             1

4          2/13/13         2/18/13               8                                             1

4          4/24/13         4/27/13               68                                           1              

4          6/3/13          6/10/13              108                                           2

I took this as interval increase per row based on the difference.  Each 91 block of days is to be a new group starting at 1 and then increasing each 91 block by 1.  So I think our solutions are pretty much the same (the output is the same).

[Haikuo]

Only for this set of data. What if the gap between two rows are larger than 91 days? say 200 days?

[uabcms]

Thanks very much.   I appreciate your time.

The code works!

Just for clarification, I may have confused the post by mentioning the 90-day intervals.  As you assumed, the intervals don't have to start

at 91, 181, etc.   As long as the interval is at least 91 days out from the initial ddate of the previous block.  (This may still be

confusing.)

Thanks again.