topic Re: Day intervals in SAS Programming

Day intervals

uabcms — Tue, 04 Mar 2014 14:38:15 GMT

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.

Re: Day intervals

Haikuo — Tue, 04 Mar 2014 15:18:54 GMT

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

Re: Day intervals

RW9 — Tue, 04 Mar 2014 15:23:14 GMT

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;

Re: Day intervals

Haikuo — Tue, 04 Mar 2014 15:43:16 GMT

"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

Re: Day intervals

RW9 — Tue, 04 Mar 2014 15:51:37 GMT

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).

Re: Day intervals

Haikuo — Tue, 04 Mar 2014 15:53:48 GMT

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

Re: Day intervals

uabcms — Tue, 04 Mar 2014 16:46:06 GMT

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.