Days between two dates of two observations within a group

[cmccor]

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.

[Tom · Accepted solution]

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.

[mkeintz]

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