Same date or within 7 days, within two variables

[Cathrine Christiansen]

Dear statalist-members,
I need to identify patients that got med1 and med2 on the same day, or within 7 days. I made a variable that identified if a patient got two or more prescriptions on the same date:
duplicates tag patID date, gen (sameday)

But that new variable also gave me some results of patients who received two types of med1, not both med1 and med2, as I am interested in.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long patID float(date med1 med2) byte sameday
76632 19493 1 . 0
76779 21626 1 . 1
76779 21626 . 1 1
76779 21781 1 . 0
76779 21896 1 . 0
76804 21509 . 1 0
76806 20612 . 1 0
76806 20685 . 1 0
76837 19620 1 . 1
76837 19620 1 . 1
76837 19709 1 . 2
76837 19709 1 . 2
76837 19709 . 1 2
76911 20368 1 . 0
76911 20403 . 1 0
76911 20458 . 1 0
76911 20511 . 1 0
76911 20581 . 1 0
77072 19852 1 . 0
77072 19950 1 . 0
77072 20485 . 1 1
77072 20485 1 . 1
77072 20544 . 1 0
77072 20850 1 . 0
77072 21565 . 1 1
77072 21565 1 . 1
77072 21595 . 1 0
77072 21862 . 1 0
77072 21875 . 1 0
77601 21867 1 . 1
77601 21867 1 . 1
77636 19463 1 . 1
77636 19463 1 . 1
77636 19600 1 . 1
77636 19600 1 . 1
77636 19884 1 . 0
77636 19977 1 . 0
77636 20123 1 . 0
77636 20301 1 . 0
77636 20593 1 . 0
77636 20678 1 . 0
77745 21265 . 1 0
77745 21803 . 1 0
77755 20982 1 . 0
end
format %tdCCYY-NN-DD date

Thank you, Cathrine

[Clyde Schechter]

The following will do it. In addition to creating a variable in your data set indicating any rx for which there is an rx of the other medicine within 7 days, it also gives you a listing of those in the Results window.

//  CONVERT med1 AND med2 TO PROPER BINARY VARIABLES
mvencode med1 med2, mv(0)

//  CREATE SEPARATE DATA SETS FOR MED1 AND MED2 RXS
frame put _all, into(working)
frame change working
keep if med1 | med2
duplicates drop
preserve
keep if med2
keep patID date
tempfile med2_rxs
save `med2_rxs'

restore
keep if med1
keep patID date

//  PUT THEM TOGETHER WHEN THE DATES AGREE WITHIN 7 DAYS
rangejoin date -7 7 using `med2_rxs', by(patID)
keep if !missing(date_U)
rename date date_med1
rename date_U date_med2

//  WRITE A LIST OF PATIDs AND CORRESPONDING DATES OF MED1 & MED2 RXS
//  WITHIN 7 DAYS.
list, noobs clean

//  NOW MARK THE ORIGINAL DATA SET TO SHOW THIS AS WELL
gen long obs_no = _n
reshape long date_med, i(obs_no)
keep patID date_med
duplicates drop
rename date_med date
frame change default
frlink m:1 patID date, frame(working)
frame drop working
replace working = 0 if missing(working)
replace working = 1 if working > 1
rename working has_rx_other_med_within_7_days

Notes:
-rangejoin- is written by Robert Picard and is available from SSC. To use -rangejoin- you must also install -rangestat-, by Robert Picard, Nick Cox, and Roberto Ferrer, also available from SSC.
This code uses frames, so it requires version 16 or later.

[Cathrine Christiansen]

Thank you so much! But is there a way to do this without having to install codes from SSC? I work in a server for sensitive data, and cannot access the internet from within there. And it is a slow and rather tedious procedure to apply for importing codes. And how can I see the result without having to list them? I have 500.000 patients, so the list would be long. Can I just use tab? I work in stata/MP 16.1.

[Andrew Musau]

* Example generated by -dataex-. To install: ssc install dataex
clear            
input long patID float(date med1 med2) byte sameday
76632 19493 1 . 0        
76779 21626 1 . 1        
76779 21626 . 1 1        
76779 21781 1 . 0        
76779 21896 1 . 0        
76804 21509 . 1 0        
76806 20612 . 1 0        
76806 20685 . 1 0        
76837 19620 1 . 1        
76837 19620 1 . 1        
76837 19709 1 . 2        
76837 19709 1 . 2        
76837 19709 . 1 2        
76911 20368 1 . 0        
76911 20403 . 1 0        
76911 20458 . 1 0        
76911 20511 . 1 0        
76911 20581 . 1 0        
77072 19852 1 . 0        
77072 19950 1 . 0        
77072 20485 . 1 1        
77072 20485 1 . 1        
77072 20544 . 1 0        
77072 20850 1 . 0        
77072 21565 . 1 1        
77072 21565 1 . 1        
77072 21595 . 1 0        
77072 21862 . 1 0        
77072 21875 . 1 0        
77601 21867 1 . 1        
77601 21867 1 . 1        
77636 19463 1 . 1        
77636 19463 1 . 1        
77636 19600 1 . 1        
77636 19600 1 . 1        
77636 19884 1 . 0        
77636 19977 1 . 0        
77636 20123 1 . 0        
77636 20301 1 . 0        
77636 20593 1 . 0        
77636 20678 1 . 0        
77745 21265 . 1 0        
77745 21803 . 1 0        
77755 20982 1 . 0        
end            
format %tdCCYY-NN-DD date

frame put patID date med1 med2, into(meds)
frame meds{
    bys patID date (med1): replace med1=med1[1]
    bys patID date (med2): replace med2=med2[1]
    contract patID date med1 med2
    xtset patID date
    gen tag= (med1==1 & (med2==1|F.med2==1|F2.med2==1|F3.med2==1| ///
             F4.med2==1|F5.med2==1|F6.med2==1|F7.med2==1)) | ///
             (med2==1 & (med1==1|F.med1==1|F2.med1==1|F3.med1==1| ///
             F4.med1==1|F5.med1==1|F6.med1==1|F7.med1==1))
    bys patID: egen wanted= max(tag)
    l, sepby(patID)
}
frlink m:1 patID date, frame(meds)
frget wanted, from(meds)
frame drop meds

Res.:

.     l, sepby(patID)

     +---------------------------------------------------------+
     | patID         date   med1   med2   _freq   tag   wanted |
     |---------------------------------------------------------|
  1. | 76632   2013-05-15      1      .       1     0        0 |
     |---------------------------------------------------------|
  2. | 76779   2019-03-18      1      1       2     1        1 |
  3. | 76779   2019-08-20      1      .       1     0        1 |
  4. | 76779   2019-12-13      1      .       1     0        1 |
     |---------------------------------------------------------|
  5. | 76804   2018-11-21      .      1       1     0        0 |
     |---------------------------------------------------------|
  6. | 76806   2016-06-07      .      1       1     0        0 |
  7. | 76806   2016-08-19      .      1       1     0        0 |
     |---------------------------------------------------------|
  8. | 76837   2013-09-19      1      .       2     0        1 |
  9. | 76837   2013-12-17      1      1       3     1        1 |
     |---------------------------------------------------------|
 10. | 76911   2015-10-07      1      .       1     0        0 |
 11. | 76911   2015-11-11      .      1       1     0        0 |
 12. | 76911   2016-01-05      .      1       1     0        0 |
 13. | 76911   2016-02-27      .      1       1     0        0 |
 14. | 76911   2016-05-07      .      1       1     0        0 |
     |---------------------------------------------------------|
 15. | 77072   2014-05-09      1      .       1     0        1 |
 16. | 77072   2014-08-15      1      .       1     0        1 |
 17. | 77072   2016-02-01      1      1       2     1        1 |
 18. | 77072   2016-03-31      .      1       1     0        1 |
 19. | 77072   2017-01-31      1      .       1     0        1 |
 20. | 77072   2019-01-16      1      1       2     1        1 |
 21. | 77072   2019-02-15      .      1       1     0        1 |
 22. | 77072   2019-11-09      .      1       1     0        1 |
 23. | 77072   2019-11-22      .      1       1     0        1 |
     |---------------------------------------------------------|
 24. | 77601   2019-11-14      1      .       2     0        0 |
     |---------------------------------------------------------|
 25. | 77636   2013-04-15      1      .       2     0        0 |
 26. | 77636   2013-08-30      1      .       2     0        0 |
 27. | 77636   2014-06-10      1      .       1     0        0 |
 28. | 77636   2014-09-11      1      .       1     0        0 |
 29. | 77636   2015-02-04      1      .       1     0        0 |
 30. | 77636   2015-08-01      1      .       1     0        0 |
 31. | 77636   2016-05-19      1      .       1     0        0 |
 32. | 77636   2016-08-12      1      .       1     0        0 |
     |---------------------------------------------------------|
 33. | 77745   2018-03-22      .      1       1     0        0 |
 34. | 77745   2019-09-11      .      1       1     0        0 |
     |---------------------------------------------------------|
 35. | 77755   2017-06-12      1      .       1     0        0 |
     +---------------------------------------------------------+

[Clyde Schechter]

I understand about working under restrictions, and I sympathize. There are ways to do this without -rangestat- and -rangejoin-. Andrew Musau has kindly posted one. My only criticism of that code is that it is designed to work with the 1/. representation for med1 and med2 that you supplied initially. (And it would need modification to work with the 1/0 representation that I transformed that to in my code.) Representing yes/no as 1/. is a common spreadsheet practice that works very well visually--and visual representation of data is what spreadsheets do best. But Stata is not a spreadsheet, and it does not see things the way human eyes do. The best that can be said about using 1/. for yes/no is that you are cutting yourself off from the ability to use logical expressions in Stata when you do that. More than that, you are setting yourself up for errors, because Stata interprets missing values as true, so that to Stata, 1/. is true/true and there is no category for false!

And how can I see the result without having to list them? I have 500.000 patients, so the list would be long.

If you re-read the very beginning of my response in #2, you will see that the listing in the Results window is just an add-on: the code also creates an indicator variable in the original data set that flags the targeted observation. You can ignore the listing. Actually, given that your data set is so large, were you to use that code, you would probably want to delete the -list- command so you don't waste time creating that listing in the first place.

So putting all of that together, here is a slightly modified version of the code in #2 that addresses both of these issues:

//  CONVERT med1 AND med2 TO PROPER BINARY VARIABLES
mvencode med1 med2, mv(0)

//  CREATE SEPARATE DATA SETS FOR MED1 AND MED2 RXS
frame put _all, into(working)
frame change working
keep if med1 | med2
duplicates drop
preserve
keep if med2
keep patID date
rename date date_U
tempfile med2_rxs
save `med2_rxs'

restore
keep if med1
keep patID date

//  PUT THEM TOGETHER WHEN THE DATES AGREE WITHIN 7 DAYS
joinby patID using `med2_rxs', unmatched(none)
keep if abs(date - date_U) <= 7
keep if !missing(date_U)
rename date date_med1
rename date_U date_med2

//  NOW MARK THE ORIGINAL DATA SET TO SHOW THIS AS WELL
gen long obs_no = _n
reshape long date_med, i(obs_no)
keep patID date_med
duplicates drop
rename date_med date
frame change default
frlink m:1 patID date, frame(working)
frame drop working
replace working = 0 if missing(working)
replace working = 1 if working > 1
rename working has_rx_other_med_within_7_days

Changed code is shown in bold face, and the command that listed results has been deleted.

[Cathrine Christiansen]

Thank you, but when I get to the command "save `med2_rxs' " I get the error message: "invalid file specification r(198)"
I have of course double checked the spelling, and I am quite sure that the character-setting is right (`')
Any clues?

[Clyde Schechter]

I tested that code before I posted it, and it ran without error messages on my setup.

My best guess is that you are trying to run this code line-by-line, or in sections. That can't work. The whole thing has to run, beginning to end, in one fell swoop from the do-editor. In Stata, when you run the code line-by-line or in pieces, any local macros defined in a piece disappear at the end of that piece. The local macro med2_rxs is defined in the -tempfile- command immediately before the -save- command that uses it. If you run those commands separately, when you try the -save-, local macro med2_rxs will have disappeared, and this will give you precisely the error message you got. Run the whole thing beginning to end without interruptions and I think you will not have any problem.

[Cathrine Christiansen]

Thank you so much! I did not know that, so I entered one line at a time to see where it might go wrong, clearly a mistake.
When I ran the do-file all at once it worked perfectly! Thank you for explaining everything in an understandable way!