Count total number overlapping days within a by group

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Count total number overlapping days within a by group

I have a dataset where each row represents a prescription filled (PersonId, DtRxStart, DtRxStop, QtrRxStart). What I need to calculate is the total number of overlapping prescription days (1+ prescription on a given day) per person per calendar quarter, as well as the total number of prescription days per person per calendar quarter. If a person has more than one drug prescribed on a given day, it should only be counted as 1 overlapping prescription day.

 

PersonId     DtRxStart     DtRxStop     QtrRxStart     Drug

1                  04/19/2012  05/03/2012   2                   A

1                  05/18/2012  06/01/2012   2                   A

1                  06/02/2012  06/16/2012   2                   A

1                  06/15/2012  06/29/2012   2                   B

1                  06/28/2012  07/12/2012   2                   A

1                  07/16/2012  07/30/2012   3                   B

2                  02/02/2012  02/06/2012   1                   C

2                  10/09/2012  10/23/2012   4                   A

2                  11/30/2012  12/09/2012   4                   B

2                  11/30/2012  12/04/2012   4                   C

2                  11/30/2012  12/15/2012   4                   A

 

Any help on this would be greatly appreciated.

 

Thanks.


Accepted Solutions
Solution
‎08-10-2016 04:13 PM
Super User
Posts: 11,343

Re: Count total number overlapping days within a by group

Using the previous TEMP data set above as a starting point somethng like this perhaps:

 

proc summary data=temp nway;
   class personid QtrRxStart PrescribedDate;
   var DtRxStart;
   output out=summary (drop=_: ) n=Drugs;
run;
data tab;
  set summary;
  overlap=(drugs>1);
run;
proc tabulate data=tab;
   class QtrRxStart;
   var overlap ;
   table overlap*(sum='days overlapping'*f=best6. n='Total days' mean='%'*f=percent8.2),
         QtrRxStart
   ;
run;

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Count total number overlapping days within a by group

Please show what you want for the output for your given example data set. There really are several ways to interpret the word "overlap" and your exception "more than one drug prescribed on a given day, it should only be counted as 1 overlapping prescription day." might be considered incomplete depending on what the output looks like.

 

Also by any chance do you actually have a prescription ID to go along with this? I suspect that could help clear up some potential issues.

 

Here is one way to identify days with more than one prescription. I am not sure what role your quarter actually plays.

data have;
   informat  DtRxStart DtRxStop mmddyy10.;
   format  DtRxStart DtRxStop mmddyy10.;
   input PersonId     DtRxStart     DtRxStop     QtrRxStart     Drug $    ;
datalines;
1                  04/19/2012  05/03/2012   2                   A
1                  05/18/2012  06/01/2012   2                   A
1                  06/02/2012  06/16/2012   2                   A
1                  06/15/2012  06/29/2012   2                   B
1                  06/28/2012  07/12/2012   2                   A
1                  07/16/2012  07/30/2012   3                   B
2                  02/02/2012  02/06/2012   1                   C
2                  10/09/2012  10/23/2012   4                   A
2                  11/30/2012  12/09/2012   4                   B
2                  11/30/2012  12/04/2012   4                   C
2                  11/30/2012  12/15/2012   4                   A
;
run;

data temp;
   set have;
   do PrescribedDate= DtRxStart to  DtRxStop;
      output;
   end;
   format PrescribedDate mmddyy10.;
run;
      
proc summary data=temp nway;
   class personid PrescribedDate;
   var DtRxStart;
   output out=summary (drop=_: where=(Drugs>1)) n=Drugs;
run;
Occasional Contributor
Posts: 15

Re: Count total number overlapping days within a by group

Ultimately, I will have to calculate the proportion of person prescription days with overlapping prescriptions by quarter. To answer your question, there is a PrescriptionId that is a unique ID that represents each prescription or each row. Quarter is just a way to measure progress toward reducing the number of overlapping prescriptions prescribed.

 

PersonId     DtRxStart     DtRxStop     QtrRxStart     Drug     PrescriptionId

1                  04/19/2012  05/03/2012   2                   A           RX01

1                  05/18/2012  06/01/2012   2                   A           RX02

1                  06/02/2012  06/16/2012   2                   A           RX03

1                  06/15/2012  06/29/2012   2                   B           RX04

1                  06/28/2012  07/12/2012   2                   A           RX05

1                  07/16/2012  07/30/2012   3                   B           RX06

2                  02/02/2012  02/06/2012   1                   C           RX07

2                  10/09/2012  10/23/2012   4                   A           RX08

2                  11/30/2012  12/09/2012   4                   B           RX09

2                  11/30/2012  12/04/2012   4                   C           RX10

2                  11/30/2012  12/15/2012   4                   A           RX11

 

Output would ultimately look something like this:

 

                                                                                             Qtr 1     Qtr 2     Qtr 4     Qtr 4

Total # of days with overlapping prescriptions (>1 Rxs):     ###       ###       ###       ###

Total # of prescription days:                                                ###       ###       ###       ###

Proportion of days with an overlapping prescription:          ##%      ##%      ##%     ##%   

Solution
‎08-10-2016 04:13 PM
Super User
Posts: 11,343

Re: Count total number overlapping days within a by group

Using the previous TEMP data set above as a starting point somethng like this perhaps:

 

proc summary data=temp nway;
   class personid QtrRxStart PrescribedDate;
   var DtRxStart;
   output out=summary (drop=_: ) n=Drugs;
run;
data tab;
  set summary;
  overlap=(drugs>1);
run;
proc tabulate data=tab;
   class QtrRxStart;
   var overlap ;
   table overlap*(sum='days overlapping'*f=best6. n='Total days' mean='%'*f=percent8.2),
         QtrRxStart
   ;
run;
Occasional Contributor
Posts: 15

Re: Count total number overlapping days within a by group

That worked thanks.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 420 views
  • 0 likes
  • 2 in conversation