BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
cmccor
Fluorite | Level 6

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:

ObservationPatientDateDays SupplyEnd date
118/25/2014309/27/2014
219/25/20143010/25/2014
3110/27/20143011/26/2014
4111/22/20143012/22/2014
5112/23/2014301/22/2015
626/26/2018307/26/2018
727/23/2018308/22/2018
828/20/2018309/19/2018
9210/30/20183011/29/2018
1035/1/2014305/31/2014
1137/25/2014308/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

ObservationPatientDateDays SupplyEnd dateGapRunning gap
118/25/2014309/27/2014..
219/25/20143010/25/2014-2-2
3110/27/20143011/26/201420
4111/22/20143012/22/2014-4-4
5112/23/2014301/22/20151-3
626/26/2018307/26/2018. 
727/23/2018308/22/2018-3-3
828/20/2018309/19/2018-2-5
9210/30/20183011/29/20184136
1035/1/2014305/31/2014. 
1137/25/2014308/24/20145555

 

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
Tom
Super User Tom
Super User

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.

 

 

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

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
PROC Star

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

--------------------------

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 2 replies
  • 513 views
  • 0 likes
  • 3 in conversation