Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Using bysort to Match Strings Across Rows

    This is supposed to be easy but not sure why I am not getting it right (just a few months into Stata):
    Please see the attached dataset. I am trying to find out if patients received a combination of three meds on each of their visits to the pharmacy. So the logic would be to sort by patients, then by date of pharmacy visit and then search for strings within each date (td). The strings would be "TDF(300mg)+3TC(300mg)+EFV(600mg)", "Cotrimoxazole 960mg" and "Isoniazid 300mg". In trying to solve the logic, I also encoded the strings as follows: "TDF(300mg)+3TC(300mg)+EFV(600mg)" - 41, "Cotrimoxazole 960mg" - 30 and "Isoniazid 300mg" - 33. I would like to generate a new variable called SameDay that returns "Yes" or "No" depending on if all three meds are administered during the visit. I have tried the following without success:
    1. bysort HospitalNum (DateVisitPharm): g SameDay = cond((Regimen[_n] == [30|33|41] & Regimen[_n+1] == [30|33|41] & Regimen[_n+2] == [30|33|41]),"Yes","No")
    2. bysort HospitalNum (DateVisitPharm): g SameDay = cond((Regimen[_n] == [30|33|41] & Regimen[_n] == [30|33|41] & Regimen[_n] == [30|33|41]),"Yes","No")
    3. bysort HospitalNum (DateVisitPharm): g SameDay = cond((Regimen[_n] == 30 & Regimen[_n] == 33 & Regimen[_n] == 41),"Yes","No")
    4. bysort HospitalNum (DateVisitPharm): egen SameDay = anymatch(Regimen1),v(30 33 41)
    5. bysort HospitalNum (DateVisitPharm): egen SameDay = anyvalue(Regimen1),v(30 33 41)
    6. bysort HospitalNum (DateVisitPharm): egen SameDay = anymatch(Regimen), values("*Cotrim*","*Isonia*")
    I would also appreciate a succinct explanation on the role of () before the : in bysort commands, in addition to any other possible combinations here including having two variables within the () and before it. If there is any advanced material on this I would appreciate it too. Also if there is any advanced material on explicit subscripting I could go through, that would be much appreciated. I know this is a bit much but I will be very appreciative.
    Attached Files
    Tags: None
  • #2
    Please read and act on https://www.statalist.org/forums/help#stata The view on spreadsheet attachments is emphatically No!. for several reasons spelled out there.

    One thing at a time. Data example please showing how data are held in Stata, including specifically whether Regimen is string or numeric.

    Comment

    • #3
      Without looking at your data, but just at your code, it seems to me you are trying to do in a single step what would be easier done in multiple steps. I believe you used the encode to create the regimen variable from your string variable, so regimen thus will be numeric. This (untested, lacking example data) modification of your code might send you in a useful direction.
      Code:
      generate wanted = cond(inlist(regimen,30,33,41),regimen,.)
bysort HospitalNum DateVisitPharm (wanted): generate SameDay = wanted[1]==30 & wanted[2]==33 & wanted[3]==41
      This assumes that any given drug is recorded only a single time on a given date.

      For your reference,
      Code:
      Regimen[_n] == [30|33|41]
      is correctly written as
      Code:
      Regimen==30 | Regimen==33 | Regimen==41
      or more succinctly, as in the example above, using the inlist() function.

      I would also appreciate a succinct explanation on the role of () before the : in bysort commands, in addition to any other possible combinations here including having two variables within the () and before it. If there is any advanced material on this I would appreciate it too. Also if there is any advanced material on explicit subscripting I could go through, that would be much appreciated. I know this is a bit much but I will be very appreciative.
      You may find this article from the Stata Journal helpful with the by comand.

      https://www.stata-journal.com/articl...article=pr0004

      The Stata User's Guide PDF section 13.7 discusses explicit subscripting, if you have not already read it.
      Last edited by William Lisowski; 12 Sep 2021, 15:28.

      Comment

      • #4
        I have just read through the link you provided and it specifically states, "If your dataset is confidential, then provide a fake example instead." I have just tried the dataex command which reveals confidential information. Please provide guidance on how to "provide a fake example instead" as what I have provided in the spreadsheet is fake, especially if the one pasted does not suffice.

        Thank you.
        Patient DateVisitPharm Regimen
        Patient A 29 Dec 1899 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient A 29 Dec 1899 TDF(300mg)+3TC(300mg)+DTG(50mg)
        Patient A 29 Dec 1899 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient A 29 Dec 1899 TDF(300mg)+3TC(300mg)+DTG(50mg)
        Patient A 29 Dec 1899 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient A 29 Dec 1899 Cotrimoxazole 960mg
        Patient A 29 Dec 1899 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient A 29 Dec 1899 TDF(300mg)+3TC(300mg)+DTG(50mg)
        Patient A 29 Dec 1899 Isoniazid 300mg
        Patient B 29 Dec 1899 Cotrimoxazole 960mg
        Patient B 13-Jan-02 TDF(300mg)+3TC(300mg)+DTG(50mg)
        Patient B 13-Jan-02 Cotrimoxazole 960mg
        Patient B 17-Jan-02 TDF(300mg)+3TC(300mg)+DTG(50mg)
        Patient B 17-Jan-02 Cotrimoxazole 960mg
        Patient B 30-Jan-02 TDF(300mg)+3TC(300mg)+DTG(50mg)
        Patient B 18-Feb-02 TDF(300mg)+3TC(300mg)+DTG(50mg)
        Patient C 18-Feb-02 Cotrimoxazole 960mg
        Patient C 18-Feb-02 Isoniazid 300mg
        Patient C 14-Oct-02 Isoniazid 300mg
        Patient C 14-Oct-02 TDF(300mg)+3TC(300mg)+DTG(50mg)
        Patient C 14-Oct-02 Cotrimoxazole 960mg
        Patient C 06-May-09 Cotrimoxazole 960mg
        Patient C 06-May-09 TDF(300mg)+3TC(300mg)+DTG(50mg)
        Patient C 06-Jan-10 TDF(300mg)+3TC(300mg)+DTG(50mg)
        Patient D 06-Jan-10 Cotrimoxazole 960mg
        Patient D 13-Jan-10 Cotrimoxazole 960mg
        Patient D 13-Jan-10 TDF(300mg)+3TC(300mg)+DTG(50mg)
        Patient D 26-May-10 Cotrimoxazole 960mg
        Patient D 26-May-10 TDF(300mg)+3TC(300mg)+DTG(50mg)
        Patient D 30-May-10 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient D 30-May-10 Cotrimoxazole 960mg
        Patient D 31-May-10 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient D 31-May-10 Cotrimoxazole 960mg
        Patient E 09-Aug-10 Cotrimoxazole 960mg
        Patient E 09-Aug-10 TDF(300mg)+3TC(300mg)+DTG(50mg)
        Patient E 08-Oct-10 TDF(300mg)+3TC(300mg)+DTG(50mg)
        Patient E 22-Oct-10 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient E 22-Oct-10 Cotrimoxazole 960mg
        Patient E 23-Oct-10 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient E 23-Oct-10 Cotrimoxazole 960mg
        Patient F 17-Feb-11 TDF(300mg)+3TC(300mg)+ATV/r(300/100mg)
        Patient F 17-Feb-11 Cotrimoxazole 960mg
        Patient F 24-Mar-11 TDF(300mg)+3TC(300mg)+DTG(50mg)
        Patient F 07-Aug-11 Cotrimoxazole 960mg
        Patient F 07-Aug-11 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient F 08-Aug-11 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient F 08-Aug-11 Cotrimoxazole 960mg
        Patient F 08-Nov-11 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient G 08-Nov-11 Cotrimoxazole 960mg
        Patient G 10-Dec-12 TDF(300mg)+3TC(300mg)+DTG(50mg)
        Patient G 10-Dec-12 Cotrimoxazole 960mg
        Patient G 16-Dec-12 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient G 14-Mar-13 Cotrimoxazole 960mg
        Patient G 14-Mar-13 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient G 13-Jul-13 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient G 13-Jul-13 Cotrimoxazole 960mg
        Patient G 11-Feb-14 TDF(300mg)+3TC(300mg)+DTG(50mg)
        Patient H 11-Feb-14 Cotrimoxazole 960mg
        Patient H 12-Mar-14 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient H 13-Apr-14 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient H 23-Jun-14 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient H 23-Jun-14 Cotrimoxazole 960mg
        Patient H 05-Jan-15 Cotrimoxazole 960mg
        Patient H 05-Jan-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient I 10-Mar-15 Cotrimoxazole 960mg
        Patient I 10-Mar-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient I 18-Apr-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient I 18-Apr-15 Cotrimoxazole 960mg
        Patient I 20-Apr-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient I 20-Apr-15 Cotrimoxazole 960mg
        Patient I 02-May-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient I 08-May-15 Cotrimoxazole 960mg
        Patient J 08-May-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient J 13-May-15 Cotrimoxazole 960mg
        Patient J 13-May-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient J 16-May-15 Cotrimoxazole 960mg
        Patient J 16-May-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient J 17-May-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient J 17-May-15 Cotrimoxazole 960mg
        Patient J 30-May-15 Cotrimoxazole 960mg
        Patient J 30-May-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient K 28-Jun-15 Cotrimoxazole 960mg
        Patient K 28-Jun-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient K 01-Jul-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient K 01-Jul-15 Cotrimoxazole 960mg
        Patient K 31-Jul-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient K 31-Jul-15 Cotrimoxazole 960mg
        Patient K 20-Aug-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient L 20-Aug-15 Cotrimoxazole 960mg
        Patient L 25-Aug-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient L 25-Aug-15 Cotrimoxazole 960mg
        Patient L 01-Sep-15 Cotrimoxazole 960mg
        Patient L 13-Sep-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient L 13-Sep-15 Cotrimoxazole 960mg
        Patient L 16-Sep-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient L 16-Sep-15 Cotrimoxazole 960mg
        Patient M 30-Sep-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient M 08-Oct-15 Cotrimoxazole 480mg
        Patient M 08-Oct-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient M 17-Oct-15 Cotrimoxazole 960mg
        Patient M 17-Oct-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient M 25-Oct-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient M 25-Oct-15 Cotrimoxazole 960mg
        Patient M 25-Oct-15 Cotrimoxazole 960mg
        Patient M 25-Oct-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient M 26-Oct-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient N 26-Oct-15 Cotrimoxazole 960mg
        Patient N 03-Nov-15 Cotrimoxazole 960mg
        Patient N 03-Nov-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient N 12-Nov-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient N 12-Nov-15 Cotrimoxazole 960mg
        Patient N 17-Nov-15 Cotrimoxazole 960mg
        Patient N 17-Nov-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient N 20-Nov-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient N 20-Nov-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient N 20-Nov-15 Cotrimoxazole 960mg
        Patient N 20-Nov-15 Cotrimoxazole 960mg
        Patient O 21-Nov-15 Cotrimoxazole 960mg
        Patient O 21-Nov-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient O 23-Nov-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient O 23-Nov-15 Cotrimoxazole 960mg
        Patient O 24-Nov-15 Cotrimoxazole 960mg
        Patient O 24-Nov-15 Cotrimoxazole 960mg
        Patient O 24-Nov-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient O 24-Nov-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient O 30-Nov-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient O 30-Nov-15 Cotrimoxazole 960mg
        Patient P 03-Dec-15 Cotrimoxazole 960mg
        Patient P 03-Dec-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient P 04-Dec-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient P 04-Dec-15 Cotrimoxazole 960mg
        Patient P 07-Dec-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient P 07-Dec-15 Cotrimoxazole 960mg
        Patient P 11-Dec-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient P 11-Dec-15 Cotrimoxazole 960mg
        Patient P 11-Dec-15 Cotrimoxazole 960mg
        Patient P 11-Dec-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient P 14-Dec-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient P 14-Dec-15 Cotrimoxazole 960mg
        Patient P 14-Dec-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient P 14-Dec-15 TDF(300mg)+3TC(300mg)+EFV(600mg)
        Patient P 14-Dec-15 Cotrimoxazole 960mg

        Comment

        • #5
          Thanks William. I will study and revert as is necessary.

          Regards


          Originally posted by William Lisowski View Post
          Without looking at your data, but just at your code, it seems to me you are trying to do in a single step what would be easier done in multiple steps. I believe you used the encode to create the regimen variable from your string variable, so regimen thus will be numeric. This (untested, lacking example data) modification of your code might send you in a useful direction.
          Code:
          generate wanted = cond(inlist(regimen,30,33,41),regimen,.)
bysort HospitalNum DateVisitPharm (wanted): generate SameDay = wanted[1]==30 & wanted[2]==33 & wanted[3]==41
          This assumes that any given drug is recorded only a single time on a given date.

          For your reference,
          Code:
          Regimen[_n] == [30|33|41]
          is correctly written as
          Code:
          Regimen==30 | Regimen==33 | Regimen==41
          or more succinctly, as in the example above, using the inlist() function.



          You may find this article from the Stata Journal helpful with the by comand.

          https://www.stata-journal.com/articl...article=pr0004

          The Stata User's Guide PDF section 13.7 discusses explicit subscripting, if you have not already read it.

          Comment

          • #6
            To create a fake dataset, it is easiest to start with a copy of your real dataset, keep only the variables of interest, change the Patient identifiers to invented values (the egen group() function can be helpful to transform them into 1, 2, ...), and then use dataex, which will display your Regimen variable using the numeric values and separately present their value labels, instead of presenting it, as you did above, displaying only its value labels rather than its values, and will display your dates as their Stata Internal Format date value instead of as a character string needing to be converted.

            Comment

            • #7
              Thank you William. Your explanation on presenting fake or 'scrubbed' data is much more helpful. My dataset is quite sensitive and I have been working on it for weeks now. Trying out any unnecessary coding calisthenics is not exactly my thing right now, but your explanation does provide some comfort in a helpful way on how to do this. This is much appreciated. I will work on it and revert accordingly. Phew!

              Originally posted by William Lisowski View Post
              To create a fake dataset, it is easiest to start with a copy of your real dataset, keep only the variables of interest, change the Patient identifiers to invented values (the egen group() function can be helpful to transform them into 1, 2, ...), and then use dataex, which will display your Regimen variable using the numeric values and separately present their value labels, instead of presenting it, as you did above, displaying only its value labels rather than its values, and will display your dates as their Stata Internal Format date value instead of as a character string needing to be converted.

              Comment

              • #8
                Please see a dataex format of the requested data below:

                Code:
                * Example generated by -dataex-. To install: ssc install dataex
clear
input float id str62 Regimen float(DateVisitPharm NextAppPharm)
1 "Cotrimoxazole 960mg"             22137 22226
1 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22137 22226
1 "Isoniazid 300mg"                 22137 22226
1 "Cotrimoxazole 960mg"             22225 22313
1 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22225 22313
1 "Isoniazid 300mg"                 22225 22313
1 "Cotrimoxazole 960mg"             22316 22403
1 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22316 22403
1 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22415 22593
1 "Cotrimoxazole 960mg"             22415 22593
2 "Isoniazid 300mg"                 22082 22174
2 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22082 22174
2 "Cotrimoxazole 960mg"             22082 22174
2 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22166 22251
2 "Cotrimoxazole 960mg"             22166 22251
2 "Cotrimoxazole 960mg"             22257 22435
2 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22257 22435
3 "Isoniazid 300mg"                 22165 22223
3 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22165 22223
3 "Cotrimoxazole 960mg"             22165 22223
3 "Isoniazid 300mg"                 22256 22434
3 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22256 22434
3 "Cotrimoxazole 960mg"             22256 22434
4 "Cotrimoxazole 960mg"             22055 22147
4 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22055 22147
4 "Isoniazid 300mg"                 22055 22147
4 "Cotrimoxazole 960mg"             22166 22251
4 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22166 22251
4 "Cotrimoxazole 960mg"             22256 22434
4 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22256 22434
5 "Cotrimoxazole 960mg"             22055 22146
5 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22055 22146
5 "Isoniazid 300mg"                 22055 22146
5 "Cotrimoxazole 960mg"             22166 22251
5 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22166 22251
5 "Cotrimoxazole 960mg"             22257 22435
5 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22257 22435
6 "Isoniazid 300mg"                 22055 22145
6 "Cotrimoxazole 960mg"             22055 22145
6 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22055 22145
6 "Cotrimoxazole 960mg"             22166 22252
6 "Isoniazid 300mg"                 22166 22252
6 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22166 22252
6 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22257 22435
6 "Cotrimoxazole 960mg"             22257 22435
7 "Isoniazid 300mg"                 22083 22177
7 "TDF(300mg)+3TC(300mg)+DTG(50mg)" 22083 22177
7 "Cotrimoxazole 960mg"             22083 22177
7 "Cotrimoxazole 960mg"             22173 22265
7 "Isoniazid 300mg"                 22173 22265
end
format %td_DD_Mon_CCYY DateVisitPharm
format %td_DD_Mon_CCYY NextAppPharm
                Originally posted by Abiye Kalaiwo View Post
                This is supposed to be easy but not sure why I am not getting it right (just a few months into Stata):
                Please see the attached dataset. I am trying to find out if patients received a combination of three meds on each of their visits to the pharmacy. So the logic would be to sort by patients, then by date of pharmacy visit and then search for strings within each date (td). The strings would be "TDF(300mg)+3TC(300mg)+EFV(600mg)", "Cotrimoxazole 960mg" and "Isoniazid 300mg". In trying to solve the logic, I also encoded the strings as follows: "TDF(300mg)+3TC(300mg)+EFV(600mg)" - 41, "Cotrimoxazole 960mg" - 30 and "Isoniazid 300mg" - 33. I would like to generate a new variable called SameDay that returns "Yes" or "No" depending on if all three meds are administered during the visit. I have tried the following without success:
                1. bysort HospitalNum (DateVisitPharm): g SameDay = cond((Regimen[_n] == [30|33|41] & Regimen[_n+1] == [30|33|41] & Regimen[_n+2] == [30|33|41]),"Yes","No")
                2. bysort HospitalNum (DateVisitPharm): g SameDay = cond((Regimen[_n] == [30|33|41] & Regimen[_n] == [30|33|41] & Regimen[_n] == [30|33|41]),"Yes","No")
                3. bysort HospitalNum (DateVisitPharm): g SameDay = cond((Regimen[_n] == 30 & Regimen[_n] == 33 & Regimen[_n] == 41),"Yes","No")
                4. bysort HospitalNum (DateVisitPharm): egen SameDay = anymatch(Regimen1),v(30 33 41)
                5. bysort HospitalNum (DateVisitPharm): egen SameDay = anyvalue(Regimen1),v(30 33 41)
                6. bysort HospitalNum (DateVisitPharm): egen SameDay = anymatch(Regimen), values("*Cotrim*","*Isonia*")
                I would also appreciate a succinct explanation on the role of () before the : in bysort commands, in addition to any other possible combinations here including having two variables within the () and before it. If there is any advanced material on this I would appreciate it too. Also if there is any advanced material on explicit subscripting I could go through, that would be much appreciated. I know this is a bit much but I will be very appreciative.

                Comment

                • #9
                  Thank you for the example data.
                  Code:
                  encode Regimen, generate(nreg)
label list nreg
generate wanted = cond(inlist(nreg,2,3,5),nreg,.)
bysort id DateVisitPharm (wanted): generate SameDay = wanted[1]==2 & wanted[2]==3 & wanted[3]==5
drop wanted
sort id DateVisitPharm Regimen
list id DateVisitPharm Regimen SameDay, sepby(DateVisitPharm) abbreviate(20)
                  Code:
                  . encode Regimen, generate(nreg)

. label list nreg
nreg:
           1 Aspirin
           2 Cotrimoxazole 960mg
           3 Isoniazid 300mg
           4 Lisinopril
           5 TDF(300mg)+3TC(300mg)+DTG(50mg)

. generate wanted = cond(inlist(nreg,2,3,5),nreg,.)
(2 missing values generated)

. bysort id DateVisitPharm (wanted): generate SameDay = wanted[1]==2 & wanted[2]==3 & wanted[3]==5

. drop wanted

. sort id DateVisitPharm Regimen

. list id DateVisitPharm Regimen SameDay, noobs sepby(DateVisitPharm) abbreviate(20)

  +-----------------------------------------------------------------+
  | id   DateVisitPharm                           Regimen   SameDay |
  |-----------------------------------------------------------------|
  |  0      04 Jul 2020                           Aspirin         0 |
  |  0      04 Jul 2020                   Isoniazid 300mg         0 |
  |  0      04 Jul 2020                        Lisinopril         0 |
  |  0      04 Jul 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         0 |
  |-----------------------------------------------------------------|
  |  1      10 Aug 2020               Cotrimoxazole 960mg         1 |
  |  1      10 Aug 2020                   Isoniazid 300mg         1 |
  |  1      10 Aug 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1 |
  |-----------------------------------------------------------------|
  |  1      06 Nov 2020               Cotrimoxazole 960mg         1 |
  |  1      06 Nov 2020                   Isoniazid 300mg         1 |
  |  1      06 Nov 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1 |
  |-----------------------------------------------------------------|
  |  1      05 Feb 2021               Cotrimoxazole 960mg         0 |
  |  1      05 Feb 2021   TDF(300mg)+3TC(300mg)+DTG(50mg)         0 |
  |-----------------------------------------------------------------|
  |  1      15 May 2021               Cotrimoxazole 960mg         0 |
  |  1      15 May 2021   TDF(300mg)+3TC(300mg)+DTG(50mg)         0 |
  |-----------------------------------------------------------------|
  |  2      16 Jun 2020               Cotrimoxazole 960mg         1 |
  |  2      16 Jun 2020                   Isoniazid 300mg         1 |
  |  2      16 Jun 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1 |
  |-----------------------------------------------------------------|
  |  2      08 Sep 2020               Cotrimoxazole 960mg         0 |
  |  2      08 Sep 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         0 |
  |-----------------------------------------------------------------|
  |  2      08 Dec 2020               Cotrimoxazole 960mg         0 |
  |  2      08 Dec 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         0 |
  |-----------------------------------------------------------------|
  |  3      07 Sep 2020               Cotrimoxazole 960mg         1 |
  |  3      07 Sep 2020                   Isoniazid 300mg         1 |
  |  3      07 Sep 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1 |
  |-----------------------------------------------------------------|
  |  3      07 Dec 2020               Cotrimoxazole 960mg         1 |
  |  3      07 Dec 2020                   Isoniazid 300mg         1 |
  |  3      07 Dec 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1 |
  |-----------------------------------------------------------------|
  |  4      20 May 2020               Cotrimoxazole 960mg         1 |
  |  4      20 May 2020                   Isoniazid 300mg         1 |
  |  4      20 May 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1 |
  |-----------------------------------------------------------------|
  |  4      08 Sep 2020               Cotrimoxazole 960mg         0 |
  |  4      08 Sep 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         0 |
  |-----------------------------------------------------------------|
  |  4      07 Dec 2020               Cotrimoxazole 960mg         0 |
  |  4      07 Dec 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         0 |
  |-----------------------------------------------------------------|
  |  5      20 May 2020               Cotrimoxazole 960mg         1 |
  |  5      20 May 2020                   Isoniazid 300mg         1 |
  |  5      20 May 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1 |
  |-----------------------------------------------------------------|
  |  5      08 Sep 2020               Cotrimoxazole 960mg         0 |
  |  5      08 Sep 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         0 |
  |-----------------------------------------------------------------|
  |  5      08 Dec 2020               Cotrimoxazole 960mg         0 |
  |  5      08 Dec 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         0 |
  |-----------------------------------------------------------------|
  |  6      20 May 2020               Cotrimoxazole 960mg         1 |
  |  6      20 May 2020                   Isoniazid 300mg         1 |
  |  6      20 May 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1 |
  |-----------------------------------------------------------------|
  |  6      08 Sep 2020               Cotrimoxazole 960mg         1 |
  |  6      08 Sep 2020                   Isoniazid 300mg         1 |
  |  6      08 Sep 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1 |
  |-----------------------------------------------------------------|
  |  6      08 Dec 2020               Cotrimoxazole 960mg         0 |
  |  6      08 Dec 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         0 |
  |-----------------------------------------------------------------|
  |  7      17 Jun 2020               Cotrimoxazole 960mg         1 |
  |  7      17 Jun 2020                   Isoniazid 300mg         1 |
  |  7      17 Jun 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1 |
  |-----------------------------------------------------------------|
  |  7      15 Sep 2020               Cotrimoxazole 960mg         0 |
  |  7      15 Sep 2020                   Isoniazid 300mg         0 |
  +-----------------------------------------------------------------+
                  I didn't know what to make of NextAppPharm so I haven't used it. Your dummy data was somewhat unrealistic (as was your data in post #4) because it only includes the three drugs you are interested in, whereas in your original data when you encoded Regimen, there were at least 41 distinct drugs. And in your dummy data, you had not encoded Regimen as you described in post #1. To create realistic code, I added id 0 to your dataset with a few other values for Regimen.

                  Again, the assumption is that your data does not include cases where the same id has two observations of the same Regimen on the same DateVisitPharm

                  Comment

                  • #10
                    Originally posted by William Lisowski View Post
                    Thank you for the example data.
                    Code:
                    encode Regimen, generate(nreg)
label list nreg
generate wanted = cond(inlist(nreg,2,3,5),nreg,.)
bysort id DateVisitPharm (wanted): generate SameDay = wanted[1]==2 & wanted[2]==3 & wanted[3]==5
drop wanted
sort id DateVisitPharm Regimen
list id DateVisitPharm Regimen SameDay, sepby(DateVisitPharm) abbreviate(20)
                    Code:
                    . encode Regimen, generate(nreg)

. label list nreg
nreg:
1 Aspirin
2 Cotrimoxazole 960mg
3 Isoniazid 300mg
4 Lisinopril
5 TDF(300mg)+3TC(300mg)+DTG(50mg)

. generate wanted = cond(inlist(nreg,2,3,5),nreg,.)
(2 missing values generated)

. bysort id DateVisitPharm (wanted): generate SameDay = wanted[1]==2 & wanted[2]==3 & wanted[3]==5

. drop wanted

. sort id DateVisitPharm Regimen

. list id DateVisitPharm Regimen SameDay, noobs sepby(DateVisitPharm) abbreviate(20)

+-----------------------------------------------------------------+
| id DateVisitPharm Regimen SameDay |
|-----------------------------------------------------------------|
| 0 04 Jul 2020 Aspirin 0 |
| 0 04 Jul 2020 Isoniazid 300mg 0 |
| 0 04 Jul 2020 Lisinopril 0 |
| 0 04 Jul 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 1 10 Aug 2020 Cotrimoxazole 960mg 1 |
| 1 10 Aug 2020 Isoniazid 300mg 1 |
| 1 10 Aug 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 1 06 Nov 2020 Cotrimoxazole 960mg 1 |
| 1 06 Nov 2020 Isoniazid 300mg 1 |
| 1 06 Nov 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 1 05 Feb 2021 Cotrimoxazole 960mg 0 |
| 1 05 Feb 2021 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 1 15 May 2021 Cotrimoxazole 960mg 0 |
| 1 15 May 2021 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 2 16 Jun 2020 Cotrimoxazole 960mg 1 |
| 2 16 Jun 2020 Isoniazid 300mg 1 |
| 2 16 Jun 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 2 08 Sep 2020 Cotrimoxazole 960mg 0 |
| 2 08 Sep 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 2 08 Dec 2020 Cotrimoxazole 960mg 0 |
| 2 08 Dec 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 3 07 Sep 2020 Cotrimoxazole 960mg 1 |
| 3 07 Sep 2020 Isoniazid 300mg 1 |
| 3 07 Sep 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 3 07 Dec 2020 Cotrimoxazole 960mg 1 |
| 3 07 Dec 2020 Isoniazid 300mg 1 |
| 3 07 Dec 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 4 20 May 2020 Cotrimoxazole 960mg 1 |
| 4 20 May 2020 Isoniazid 300mg 1 |
| 4 20 May 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 4 08 Sep 2020 Cotrimoxazole 960mg 0 |
| 4 08 Sep 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 4 07 Dec 2020 Cotrimoxazole 960mg 0 |
| 4 07 Dec 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 5 20 May 2020 Cotrimoxazole 960mg 1 |
| 5 20 May 2020 Isoniazid 300mg 1 |
| 5 20 May 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 5 08 Sep 2020 Cotrimoxazole 960mg 0 |
| 5 08 Sep 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 5 08 Dec 2020 Cotrimoxazole 960mg 0 |
| 5 08 Dec 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 6 20 May 2020 Cotrimoxazole 960mg 1 |
| 6 20 May 2020 Isoniazid 300mg 1 |
| 6 20 May 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 6 08 Sep 2020 Cotrimoxazole 960mg 1 |
| 6 08 Sep 2020 Isoniazid 300mg 1 |
| 6 08 Sep 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 6 08 Dec 2020 Cotrimoxazole 960mg 0 |
| 6 08 Dec 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 7 17 Jun 2020 Cotrimoxazole 960mg 1 |
| 7 17 Jun 2020 Isoniazid 300mg 1 |
| 7 17 Jun 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 7 15 Sep 2020 Cotrimoxazole 960mg 0 |
| 7 15 Sep 2020 Isoniazid 300mg 0 |
+-----------------------------------------------------------------+
                    I didn't know what to make of NextAppPharm so I haven't used it. Your dummy data was somewhat unrealistic (as was your data in post #4) because it only includes the three drugs you are interested in, whereas in your original data when you encoded Regimen, there were at least 41 distinct drugs. And in your dummy data, you had not encoded Regimen as you described in post #1. To create realistic code, I added id 0 to your dataset with a few other values for Regimen.

                    Again, the assumption is that your data does not include cases where the same id has two observations of the same Regimen on the same DateVisitPharm
                    Thanks William for your assistance. I have taken the last couple of days to study your logic, approach and thinking. First off, let me respond to the issues you raised. As regards the drugs, my dataset has roughly 500k obs. Apparently dataex does seems to not have the flexibility to select a 'balanced' slice of the data, or probably I don't know how to use it properly. The first one I shared in excel which you referred to was carefully crafted. The insistence on dataex was my limitation there. Secondly, The assumption that my data does not include cases where the same id has two obs of the same Regimen on the same date does not apply - it does in numerous combinations including triplicates in some cases. Thirdly, NextAppPharm was another unrelated variable I generated - apologies for including it. I tried so many things but was seemed to work for me was using egen total with the numeric values of the drugs and then tabbed. See below:

                    bys HospitalNum DateVisitPharm(wanted): egen MedComboPVisit = total(wanted)

                    . tab MedComboPVisit

                    MedComboPVi |
                    sit | Freq. Percent Cum.
                    ------------+-----------------------------------
                    0 | 14,378 2.94 2.94
                    30 | 99,993 20.47 23.41
                    33 | 1,801 0.37 23.78
                    41 | 53,571 10.96 34.74
                    60 | 38 0.01 34.75
                    63 | 3,920 0.80 35.55
                    66 | 228 0.05 35.60
                    71 | 188,473 38.58 74.17
                    74 | 23,205 4.75 78.92
                    82 | 92 0.02 78.94
                    96 | 28 0.01 78.95
                    101 | 133 0.03 78.97
                    104 | 99,501 20.37 99.34
                    107 | 2,436 0.50 99.84
                    112 | 535 0.11 99.95
                    115 | 15 0.00 99.95
                    134 | 28 0.01 99.96
                    137 | 96 0.02 99.98
                    142 | 12 0.00 99.98
                    145 | 80 0.02 100.00
                    175 | 5 0.00 100.00
                    208 | 18 0.00 100.00
                    ------------+-----------------------------------
                    Total | 488,586 100.00
                    This was based on the fact that Cotrimoxazole was 30, Isoniazid was 33 and TLD was 41. So if I add these three I will get 74 with the assumption that no other form of combination of the medications would give 74. I am not sure how this would pan out if 'wanted' was not created. For now I am ok with what I have but open to other suggestions. The real problem is Stata's limitation in egen functions that work across rows for string variables. I'm sorry if I sound off but I am just about three months into Stata with no formal training. Thank you once again for your assistance William.

                    Comment

                    • #11
                      Originally posted by William Lisowski View Post
                      Thank you for the example data.
                      Code:
                      encode Regimen, generate(nreg)
label list nreg
generate wanted = cond(inlist(nreg,2,3,5),nreg,.)
bysort id DateVisitPharm (wanted): generate SameDay = wanted[1]==2 & wanted[2]==3 & wanted[3]==5
drop wanted
sort id DateVisitPharm Regimen
list id DateVisitPharm Regimen SameDay, sepby(DateVisitPharm) abbreviate(20)
                      Code:
                      . encode Regimen, generate(nreg)

. label list nreg
nreg:
1 Aspirin
2 Cotrimoxazole 960mg
3 Isoniazid 300mg
4 Lisinopril
5 TDF(300mg)+3TC(300mg)+DTG(50mg)

. generate wanted = cond(inlist(nreg,2,3,5),nreg,.)
(2 missing values generated)

. bysort id DateVisitPharm (wanted): generate SameDay = wanted[1]==2 & wanted[2]==3 & wanted[3]==5

. drop wanted

. sort id DateVisitPharm Regimen

. list id DateVisitPharm Regimen SameDay, noobs sepby(DateVisitPharm) abbreviate(20)

+-----------------------------------------------------------------+
| id DateVisitPharm Regimen SameDay |
|-----------------------------------------------------------------|
| 0 04 Jul 2020 Aspirin 0 |
| 0 04 Jul 2020 Isoniazid 300mg 0 |
| 0 04 Jul 2020 Lisinopril 0 |
| 0 04 Jul 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 1 10 Aug 2020 Cotrimoxazole 960mg 1 |
| 1 10 Aug 2020 Isoniazid 300mg 1 |
| 1 10 Aug 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 1 06 Nov 2020 Cotrimoxazole 960mg 1 |
| 1 06 Nov 2020 Isoniazid 300mg 1 |
| 1 06 Nov 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 1 05 Feb 2021 Cotrimoxazole 960mg 0 |
| 1 05 Feb 2021 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 1 15 May 2021 Cotrimoxazole 960mg 0 |
| 1 15 May 2021 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 2 16 Jun 2020 Cotrimoxazole 960mg 1 |
| 2 16 Jun 2020 Isoniazid 300mg 1 |
| 2 16 Jun 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 2 08 Sep 2020 Cotrimoxazole 960mg 0 |
| 2 08 Sep 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 2 08 Dec 2020 Cotrimoxazole 960mg 0 |
| 2 08 Dec 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 3 07 Sep 2020 Cotrimoxazole 960mg 1 |
| 3 07 Sep 2020 Isoniazid 300mg 1 |
| 3 07 Sep 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 3 07 Dec 2020 Cotrimoxazole 960mg 1 |
| 3 07 Dec 2020 Isoniazid 300mg 1 |
| 3 07 Dec 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 4 20 May 2020 Cotrimoxazole 960mg 1 |
| 4 20 May 2020 Isoniazid 300mg 1 |
| 4 20 May 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 4 08 Sep 2020 Cotrimoxazole 960mg 0 |
| 4 08 Sep 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 4 07 Dec 2020 Cotrimoxazole 960mg 0 |
| 4 07 Dec 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 5 20 May 2020 Cotrimoxazole 960mg 1 |
| 5 20 May 2020 Isoniazid 300mg 1 |
| 5 20 May 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 5 08 Sep 2020 Cotrimoxazole 960mg 0 |
| 5 08 Sep 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 5 08 Dec 2020 Cotrimoxazole 960mg 0 |
| 5 08 Dec 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 6 20 May 2020 Cotrimoxazole 960mg 1 |
| 6 20 May 2020 Isoniazid 300mg 1 |
| 6 20 May 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 6 08 Sep 2020 Cotrimoxazole 960mg 1 |
| 6 08 Sep 2020 Isoniazid 300mg 1 |
| 6 08 Sep 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 6 08 Dec 2020 Cotrimoxazole 960mg 0 |
| 6 08 Dec 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 0 |
|-----------------------------------------------------------------|
| 7 17 Jun 2020 Cotrimoxazole 960mg 1 |
| 7 17 Jun 2020 Isoniazid 300mg 1 |
| 7 17 Jun 2020 TDF(300mg)+3TC(300mg)+DTG(50mg) 1 |
|-----------------------------------------------------------------|
| 7 15 Sep 2020 Cotrimoxazole 960mg 0 |
| 7 15 Sep 2020 Isoniazid 300mg 0 |
+-----------------------------------------------------------------+
                      I didn't know what to make of NextAppPharm so I haven't used it. Your dummy data was somewhat unrealistic (as was your data in post #4) because it only includes the three drugs you are interested in, whereas in your original data when you encoded Regimen, there were at least 41 distinct drugs. And in your dummy data, you had not encoded Regimen as you described in post #1. To create realistic code, I added id 0 to your dataset with a few other values for Regimen.

                      Again, the assumption is that your data does not include cases where the same id has two observations of the same Regimen on the same DateVisitPharm
                      Sorry I forgot to add that I did not understand the role of & in the syntax below:
                      bysort id DateVisitPharm (wanted): generate SameDay = wanted[1]==2 & wanted[2]==3 & wanted[3]==5

                      Comment

                      • #12
                        Here is a revised version of the code from post #9 which will handle cases where the same drug appears multiple times for an id on a single DateVisitPharm. Observation 11 demonstrates that. In post #9, for some reaso the Statalist Forum software translated "&" to the HTML encoded version "&". I don't think that will happen in this post. If it does, where you see "&" read "&".

                        Code:
                        encode Regimen, generate(nreg)
label list nreg
sort id DateVisitPharm
generate wanted = cond(inlist(nreg,2,3,5),nreg,.)
by id DateVisitPharm: egen hasC = max(nreg==2)
by id DateVisitPharm: egen hasI = max(nreg==3)
by id DateVisitPharm: egen hasT = max(nreg==5)
generate SameDay = hasC==1 & hasI==1 & hasT==1
sort id DateVisitPharm Regimen
list id DateVisitPharm Regimen SameDay has*, sepby(DateVisitPharm) abbreviate(20)
                        Code:
                        . encode Regimen, generate(nreg)

. label list nreg
nreg:
           1 Aspirin
           2 Cotrimoxazole 960mg
           3 Isoniazid 300mg
           4 Lisinopriol
           5 TDF(300mg)+3TC(300mg)+DTG(50mg)

. sort id DateVisitPharm

. generate wanted = cond(inlist(nreg,2,3,5),nreg,.)
(2 missing values generated)

. by id DateVisitPharm: egen hasC = max(nreg==2)

. by id DateVisitPharm: egen hasI = max(nreg==3)

. by id DateVisitPharm: egen hasT = max(nreg==5)

. generate SameDay = hasC==1 & hasI==1 & hasT==1

. sort id DateVisitPharm Regimen

. list id DateVisitPharm Regimen SameDay has*, sepby(DateVisitPharm) abbreviate(20)

     +--------------------------------------------------------------------------------------+
     | id   DateVisitPharm                           Regimen   SameDay   hasC   hasI   hasT |
     |--------------------------------------------------------------------------------------|
  1. |  0      04 Jul 2020                           Aspirin         0      1      0      1 |
  2. |  0      04 Jul 2020               Cotrimoxazole 960mg         0      1      0      1 |
  3. |  0      04 Jul 2020                       Lisinopriol         0      1      0      1 |
  4. |  0      04 Jul 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         0      1      0      1 |
     |--------------------------------------------------------------------------------------|
  5. |  1      10 Aug 2020               Cotrimoxazole 960mg         1      1      1      1 |
  6. |  1      10 Aug 2020                   Isoniazid 300mg         1      1      1      1 |
  7. |  1      10 Aug 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1      1      1      1 |
     |--------------------------------------------------------------------------------------|
  8. |  1      06 Nov 2020               Cotrimoxazole 960mg         1      1      1      1 |
  9. |  1      06 Nov 2020                   Isoniazid 300mg         1      1      1      1 |
 10. |  1      06 Nov 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1      1      1      1 |
     |--------------------------------------------------------------------------------------|
 11. |  1      05 Feb 2021               Cotrimoxazole 960mg         0      1      0      1 |
 12. |  1      05 Feb 2021   TDF(300mg)+3TC(300mg)+DTG(50mg)         0      1      0      1 |
     |--------------------------------------------------------------------------------------|
 13. |  1      15 May 2021               Cotrimoxazole 960mg         0      1      0      1 |
 14. |  1      15 May 2021   TDF(300mg)+3TC(300mg)+DTG(50mg)         0      1      0      1 |
     |--------------------------------------------------------------------------------------|
 15. |  2      16 Jun 2020               Cotrimoxazole 960mg         1      1      1      1 |
 16. |  2      16 Jun 2020                   Isoniazid 300mg         1      1      1      1 |
 17. |  2      16 Jun 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1      1      1      1 |
     |--------------------------------------------------------------------------------------|
 18. |  2      08 Sep 2020               Cotrimoxazole 960mg         0      1      0      1 |
 19. |  2      08 Sep 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         0      1      0      1 |
     |--------------------------------------------------------------------------------------|
 20. |  2      08 Dec 2020               Cotrimoxazole 960mg         0      1      0      1 |
 21. |  2      08 Dec 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         0      1      0      1 |
     |--------------------------------------------------------------------------------------|
 22. |  3      07 Sep 2020               Cotrimoxazole 960mg         1      1      1      1 |
 23. |  3      07 Sep 2020                   Isoniazid 300mg         1      1      1      1 |
 24. |  3      07 Sep 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1      1      1      1 |
     |--------------------------------------------------------------------------------------|
 25. |  3      07 Dec 2020               Cotrimoxazole 960mg         1      1      1      1 |
 26. |  3      07 Dec 2020                   Isoniazid 300mg         1      1      1      1 |
 27. |  3      07 Dec 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1      1      1      1 |
     |--------------------------------------------------------------------------------------|
 28. |  4      20 May 2020               Cotrimoxazole 960mg         1      1      1      1 |
 29. |  4      20 May 2020                   Isoniazid 300mg         1      1      1      1 |
 30. |  4      20 May 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1      1      1      1 |
     |--------------------------------------------------------------------------------------|
 31. |  4      08 Sep 2020               Cotrimoxazole 960mg         0      1      0      1 |
 32. |  4      08 Sep 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         0      1      0      1 |
     |--------------------------------------------------------------------------------------|
 33. |  4      07 Dec 2020               Cotrimoxazole 960mg         0      1      0      1 |
 34. |  4      07 Dec 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         0      1      0      1 |
     |--------------------------------------------------------------------------------------|
 35. |  5      20 May 2020               Cotrimoxazole 960mg         1      1      1      1 |
 36. |  5      20 May 2020                   Isoniazid 300mg         1      1      1      1 |
 37. |  5      20 May 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1      1      1      1 |
     |--------------------------------------------------------------------------------------|
 38. |  5      08 Sep 2020               Cotrimoxazole 960mg         0      1      0      1 |
 39. |  5      08 Sep 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         0      1      0      1 |
     |--------------------------------------------------------------------------------------|
 40. |  5      08 Dec 2020               Cotrimoxazole 960mg         0      1      0      1 |
 41. |  5      08 Dec 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         0      1      0      1 |
     |--------------------------------------------------------------------------------------|
 42. |  6      20 May 2020               Cotrimoxazole 960mg         1      1      1      1 |
 43. |  6      20 May 2020                   Isoniazid 300mg         1      1      1      1 |
 44. |  6      20 May 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1      1      1      1 |
     |--------------------------------------------------------------------------------------|
 45. |  6      08 Sep 2020               Cotrimoxazole 960mg         1      1      1      1 |
 46. |  6      08 Sep 2020                   Isoniazid 300mg         1      1      1      1 |
 47. |  6      08 Sep 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1      1      1      1 |
     |--------------------------------------------------------------------------------------|
 48. |  6      08 Dec 2020               Cotrimoxazole 960mg         0      1      0      1 |
 49. |  6      08 Dec 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         0      1      0      1 |
     |--------------------------------------------------------------------------------------|
 50. |  7      17 Jun 2020               Cotrimoxazole 960mg         1      1      1      1 |
 51. |  7      17 Jun 2020                   Isoniazid 300mg         1      1      1      1 |
 52. |  7      17 Jun 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1      1      1      1 |
     |--------------------------------------------------------------------------------------|
 53. |  7      15 Sep 2020               Cotrimoxazole 960mg         0      1      1      0 |
 54. |  7      15 Sep 2020                   Isoniazid 300mg         0      1      1      0 |
     |--------------------------------------------------------------------------------------|
 55. | 11      10 Aug 2020               Cotrimoxazole 960mg         1      1      1      1 |
 56. | 11      10 Aug 2020               Cotrimoxazole 960mg         1      1      1      1 |
 57. | 11      10 Aug 2020                   Isoniazid 300mg         1      1      1      1 |
 58. | 11      10 Aug 2020   TDF(300mg)+3TC(300mg)+DTG(50mg)         1      1      1      1 |
     +--------------------------------------------------------------------------------------+
                        • 1 like

                        Comment

                        • #13
                          In post #12 above, the "generate wanted" command can and should be deleted; it is no longer needed in the code. I would edit the post and remove it, but I suspect that editing led to the problem the Forum software introduced into the code in post #9, so I'll leave post #12 unedited.

                          Comment

                          Previous Next
                          Working...
                          X