Fluorite | Level 6

## Days between two dates of two observations within a group

Hi,

I am trying to calculate the gap days between when a medication runs out and the day of a refill within patients, and the total of the gap days for the patient. I have the below data cut example:

 Observation Patient Date Days Supply End date 1 1 8/25/2014 30 9/27/2014 2 1 9/25/2014 30 10/25/2014 3 1 10/27/2014 30 11/26/2014 4 1 11/22/2014 30 12/22/2014 5 1 12/23/2014 30 1/22/2015 6 2 6/26/2018 30 7/26/2018 7 2 7/23/2018 30 8/22/2018 8 2 8/20/2018 30 9/19/2018 9 2 10/30/2018 30 11/29/2018 10 3 5/1/2014 30 5/31/2014 11 3 7/25/2014 30 8/24/2014

For example, for patient one, I want to calculate the days between 9/27/2014 and 9/25/2014, since that is the last day of medication dispensing in observation 1 and the date they got a refill on observation 2. Same for the gap between observation 2 and 3, 3 and 4, etc. as long as it is within the individual patient. Then I need a running total of all the gap days for each patient, adding each additional gap days within the patient.

So the final output I want should look something like this

 Observation Patient Date Days Supply End date Gap Running gap 1 1 8/25/2014 30 9/27/2014 . . 2 1 9/25/2014 30 10/25/2014 -2 -2 3 1 10/27/2014 30 11/26/2014 2 0 4 1 11/22/2014 30 12/22/2014 -4 -4 5 1 12/23/2014 30 1/22/2015 1 -3 6 2 6/26/2018 30 7/26/2018 . 7 2 7/23/2018 30 8/22/2018 -3 -3 8 2 8/20/2018 30 9/19/2018 -2 -5 9 2 10/30/2018 30 11/29/2018 41 36 10 3 5/1/2014 30 5/31/2014 . 11 3 7/25/2014 30 8/24/2014 55 55

I tried getting it to work but can't get the system to restart every time it gets to a new patient and to calculate both the gap between observations and the running gap.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Days between two dates of two observations within a group

I don't think your RUNNING_GAP variable makes any sense.  You cannot teleport the extra pills you got in September back to the days in July where you ran out of pills.

Perhaps looking at it this way is more helpful?

``````data have;
input Patient Date :mmddyy. Days_Supply End_date :mmddyy.;
format date end_date yymmdd10.;
cards;
1 8/25/2014 30 9/27/2014
1 9/25/2014 30 10/25/2014
1 10/27/2014 30 11/26/2014
1 11/22/2014 30 12/22/2014
1 12/23/2014 30 1/22/2015
2 6/26/2018 30 7/26/2018
2 7/23/2018 30 8/22/2018
2 8/20/2018 30 9/19/2018
2 10/30/2018 30 11/29/2018
3 5/1/2014 30 5/31/2014
3 7/25/2014 30 8/24/2014
;

data want;
set have;
by patient;
set have(firstobs=2 keep=date rename=(date=next_fill)) have(obs=1 drop=_all_);
if first.patient then extra=0;
if last.patient then next_fill=.;
else days = next_fill - date ;
if days > days_supply then gap = days_supply - days;
else extra+(days_supply-days);
if first.patient then running_gap=0;
running_gap + gap;
run;

proc print;
run;``````

Result:

```                                 Days_                                                        running_
Obs    Patient          Date    Supply      End_date     next_fill    extra    days    gap       gap

1       1       2014-08-25      30      2014-09-27    2014-09-25      0       31      -1        -1
2       1       2014-09-25      30      2014-10-25    2014-10-27      0       32      -2        -3
3       1       2014-10-27      30      2014-11-26    2014-11-22      4       26       .        -3
4       1       2014-11-22      30      2014-12-22    2014-12-23      4       31      -1        -4
5       1       2014-12-23      30      2015-01-22             .      4        .       .        -4
6       2       2018-06-26      30      2018-07-26    2018-07-23      3       27       .         0
7       2       2018-07-23      30      2018-08-22    2018-08-20      5       28       .         0
8       2       2018-08-20      30      2018-09-19    2018-10-30      5       71     -41       -41
9       2       2018-10-30      30      2018-11-29             .      5        .       .       -41
10       3       2014-05-01      30      2014-05-31    2014-07-25      0       85     -55       -55
11       3       2014-07-25      30      2014-08-24             .      0        .       .       -55
```

You might want to enhance the algorithm to allow the patient to use the extra pills.  So the first patient would only have a total of 3 missed days instead of 4 since they could have used some of the four extra pills from the third month to cover the extra day in the fourth month.

2 REPLIES 2
Super User

## Re: Days between two dates of two observations within a group

I don't think your RUNNING_GAP variable makes any sense.  You cannot teleport the extra pills you got in September back to the days in July where you ran out of pills.

Perhaps looking at it this way is more helpful?

``````data have;
input Patient Date :mmddyy. Days_Supply End_date :mmddyy.;
format date end_date yymmdd10.;
cards;
1 8/25/2014 30 9/27/2014
1 9/25/2014 30 10/25/2014
1 10/27/2014 30 11/26/2014
1 11/22/2014 30 12/22/2014
1 12/23/2014 30 1/22/2015
2 6/26/2018 30 7/26/2018
2 7/23/2018 30 8/22/2018
2 8/20/2018 30 9/19/2018
2 10/30/2018 30 11/29/2018
3 5/1/2014 30 5/31/2014
3 7/25/2014 30 8/24/2014
;

data want;
set have;
by patient;
set have(firstobs=2 keep=date rename=(date=next_fill)) have(obs=1 drop=_all_);
if first.patient then extra=0;
if last.patient then next_fill=.;
else days = next_fill - date ;
if days > days_supply then gap = days_supply - days;
else extra+(days_supply-days);
if first.patient then running_gap=0;
running_gap + gap;
run;

proc print;
run;``````

Result:

```                                 Days_                                                        running_
Obs    Patient          Date    Supply      End_date     next_fill    extra    days    gap       gap

1       1       2014-08-25      30      2014-09-27    2014-09-25      0       31      -1        -1
2       1       2014-09-25      30      2014-10-25    2014-10-27      0       32      -2        -3
3       1       2014-10-27      30      2014-11-26    2014-11-22      4       26       .        -3
4       1       2014-11-22      30      2014-12-22    2014-12-23      4       31      -1        -4
5       1       2014-12-23      30      2015-01-22             .      4        .       .        -4
6       2       2018-06-26      30      2018-07-26    2018-07-23      3       27       .         0
7       2       2018-07-23      30      2018-08-22    2018-08-20      5       28       .         0
8       2       2018-08-20      30      2018-09-19    2018-10-30      5       71     -41       -41
9       2       2018-10-30      30      2018-11-29             .      5        .       .       -41
10       3       2014-05-01      30      2014-05-31    2014-07-25      0       85     -55       -55
11       3       2014-07-25      30      2014-08-24             .      0        .       .       -55
```

You might want to enhance the algorithm to allow the patient to use the extra pills.  So the first patient would only have a total of 3 missed days instead of 4 since they could have used some of the four extra pills from the third month to cover the extra day in the fourth month.

PROC Star

## Re: Days between two dates of two observations within a group

If it's just a matter of generating the sample results you posted, then:

``````data have;
input Patient Date :mmddyy. Days_Supply End_date :mmddyy.;
format date end_date yymmdd10.;
cards;
1 8/25/2014 30 9/27/2014
1 9/25/2014 30 10/25/2014
1 10/27/2014 30 11/26/2014
1 11/22/2014 30 12/22/2014
1 12/23/2014 30 1/22/2015
2 6/26/2018 30 7/26/2018
2 7/23/2018 30 8/22/2018
2 8/20/2018 30 9/19/2018
2 10/30/2018 30 11/29/2018
3 5/1/2014 30 5/31/2014
3 7/25/2014 30 8/24/2014
;

data want;
set have;
by patient;
gap = date-lag(end_date);
running_gap+gap;
if first.patient then call missing(gap,running_gap);
run;``````

This will generate a note on the log, saying:

```NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
1 at LLL:cc
```

That's because the lag(end_date) expression will return a missing value for the first observation in HAVE.  In turn that will produce the note when the statement

``  gap = date-lag(end_date);``

tries to subtract that missing value

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Discussion stats
• 2 replies
• 513 views
• 0 likes
• 3 in conversation