calculating individual changes - long format data

[Natallia Lapitskaya]

Dear friends,
asking kindly for your help!!!! apologize is the question too simple for many of you....I got lost:-)
attached is the example of my dataset (sorry for attachment, did not managed to post it in this text to get it clearly): here is one patient (id number 11) in long format -each line indicates one medication, he/she gets medication coded with ATC (m_kode), dosage, dosage_antal (amout) , dosage_enhed (mg or grams etc), WAVE indicates the point when I looked at medication: 1- baseline, 2- middleway, 3-outcome 90 days.

I need to se if I made a dosis change:

I generated new variables : medicine_dosage1(baseline),medicine_dosage2(middle way), medicine_dosage3 (outcome)

I can see I can not just say : generate dosis_change=medicine_dasage3-medicine_dosage1

Please, give me a little hint if I am moving wrong and how I can handle it?
Should I reshape to wide to be able to do it?

1000 thanks in advance!
Kindly, Natallia

[William Lisowski]

Here is your data imported back into Stata and presented using the dataex command, as discussed in the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question, and how to present sample data. Please note especially that many members do not use Microsoft software or, if they do, will not open documents from unfamiliar sources. In my case, I used software that can read Word documents but does not process embedded macros and the like.

I don't have time to address your question at the moment, but present this to assist other members.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id str7 m_kode int dosis byte dosis_antal str3 dosis_enhed byte wave int(medicine_dosage1 medicine_dosage2 medicine_dosage3)
11 "N06AB06"  50 1 "mg"  1   50    .    .
11 "A12A"      1 2 "stk" 1    2    .    .
11 "N02BE01" 500 8 "mg"  2    . 4000    .
11 "B01AC06"  75 1 "mg"  2    .   75    .
11 "N02AX02"  50 1 "mg"  1   50    .    .
11 "N06AX11"  30 1 "mg"  1   30    .    .
11 "N06AX11"  30 1 "mg"  3    .    .   30
11 "A12A"      1 2 "stk" 2    .    2    .
11 "M05BA04"  10 1 "mg"  3    .    .   10
11 "A02BC02"  40 1 "mg"  2    .   40    .
11 "B01AC06"  75 1 "mg"  3    .    .   75
11 "A12A"      1 2 "stk" 3    .    .    2
11 "C10AA01"  20 1 "mg"  2    .   20    .
11 "N06AX11"  30 1 "mg"  2    .   30    .
11 "B01AC06"  75 1 "mg"  1   75    .    .
11 "N06AB06"  50 1 "mg"  3    .    .   50
11 "M05BA04"  10 1 "mg"  2    .   10    .
11 "A02BC02"  40 1 "mg"  3    .    .   40
11 "N02BE01" 500 8 "mg"  3    .    . 4000
11 "N02BE01" 500 6 "mg"  1 3000    .    .
11 "M05BA04"  10 1 "mg"  1   10    .    .
11 "C10AA01"  20 1 "mg"  3    .    .   20
11 "C10AA01"  20 1 "mg"  1   20    .    .
11 "A02BC02"  40 1 "mg"  1   40    .    .
11 "N06AB06"  50 1 "mg"  2    .   50    .
end

[Natallia Lapitskaya]

Dear William, thanks you very much for this advice on posting and data presentation. I do apologize for hurry up post data in word.
Sinserely, Natallia

[William Lisowski]

Thank you for your patience, I'm back from breakfast and have looked at your problem.

You do not need to reshape to wide. The idea behind it is, for each id/m_kode combination, to compare each wave to the the last wave. That way, if any wave is different, one of the waves won't be the same as the last wave, and the change will be detected.

Here is code that does that, starting from the sample data posted above and creating the dosis_change variable. You will see that I check that both the dosage amount and the dosage units are the same.

drop medicine_dosage*
generate c = 0
by m_kode dosis (wave), sort: replace c = 1 if dosis_antal!=dosis_antal[_N]
by m_kode dosis (wave), sort: replace c = 1 if dosis_enhed!=dosis_enhed[_N]
by m_kode dosis (wave), sort: egen dosis_change = max(c)
list, sepby(m_kode dosis) abbreviate(12)

. list, sepby(m_kode dosis) abbreviate(12)

     +----------------------------------------------------------------------------+
     | id    m_kode   dosis   dosis_antal   dosis_enhed   wave   c   dosis_change |
     |----------------------------------------------------------------------------|
  1. | 11   A02BC02      40             1            mg      1   0              0 |
  2. | 11   A02BC02      40             1            mg      2   0              0 |
  3. | 11   A02BC02      40             1            mg      3   0              0 |
     |----------------------------------------------------------------------------|
  4. | 11      A12A       1             2           stk      1   0              0 |
  5. | 11      A12A       1             2           stk      2   0              0 |
  6. | 11      A12A       1             2           stk      3   0              0 |
     |----------------------------------------------------------------------------|
  7. | 11   B01AC06      75             1            mg      1   0              0 |
  8. | 11   B01AC06      75             1            mg      2   0              0 |
  9. | 11   B01AC06      75             1            mg      3   0              0 |
     |----------------------------------------------------------------------------|
 10. | 11   C10AA01      20             1            mg      1   0              0 |
 11. | 11   C10AA01      20             1            mg      2   0              0 |
 12. | 11   C10AA01      20             1            mg      3   0              0 |
     |----------------------------------------------------------------------------|
 13. | 11   M05BA04      10             1            mg      1   0              0 |
 14. | 11   M05BA04      10             1            mg      2   0              0 |
 15. | 11   M05BA04      10             1            mg      3   0              0 |
     |----------------------------------------------------------------------------|
 16. | 11   N02AX02      50             1            mg      1   0              0 |
     |----------------------------------------------------------------------------|
 17. | 11   N02BE01     500             6            mg      1   1              1 |
 18. | 11   N02BE01     500             8            mg      2   0              1 |
 19. | 11   N02BE01     500             8            mg      3   0              1 |
     |----------------------------------------------------------------------------|
 20. | 11   N06AB06      50             1            mg      1   0              0 |
 21. | 11   N06AB06      50             1            mg      2   0              0 |
 22. | 11   N06AB06      50             1            mg      3   0              0 |
     |----------------------------------------------------------------------------|
 23. | 11   N06AX11      30             1            mg      1   0              0 |
 24. | 11   N06AX11      30             1            mg      2   0              0 |
 25. | 11   N06AX11      30             1            mg      3   0              0 |
     +----------------------------------------------------------------------------+

[Natallia Lapitskaya]

Dear William, I does really help! Thanks so much.
Is it possible to command to see if the dosis_changes are incresed/decreased/discontinuated/started?

Like in this patient I see I discontinuated one drug (data line 16. as its only present in wave 1)
but increased dosage of the othe drug: line 17-18?

Thanks so much for your help!
Natallia

[William Lisowski]

I need to start by correcting an oversight in yesterday's code. I forgot to include the patient ID in my code. So in each command where you see

m_kode

you should see

id m_kode

I see now that I did not understand that dosis was the dosage and dosis_antal was the frequency, and I should have compared

by id m_kode (wave), sort: replace c = 1 if (dosis*dosis_antal)!=(dosis[_N]*dosis_antal[_N])

to achieve what you were looking for. I think, because I know nothing about administration of medicine, and was just looking for things that changed.

You're now looking for more precise things, like increases or decreases in the dosage. Because you only have at most 3 observations per medication, I think shifting to a wide layout will make it easier for you to look for specific things. Like, what would you want if the patient got a medication at baseline, but not midway, but again at followup, at a different dosage than at baseline?

Perhaps the following approach will set you on a useful path of constructing various medication-level indicators.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id str7 m_kode byte wave int dosis byte dosis_antal str3 dosis_enhed
11 "A02BC02" 1  40 1 "mg" 
11 "A02BC02" 2  40 1 "mg" 
11 "A02BC02" 3  40 1 "mg" 
11 "A12A"    1   1 2 "stk"
11 "A12A"    2   1 2 "stk"
11 "A12A"    3   1 2 "stk"
11 "B01AC06" 1  75 1 "mg" 
11 "B01AC06" 2  75 1 "mg" 
11 "B01AC06" 3  75 1 "mg" 
11 "C10AA01" 1  20 1 "mg" 
11 "C10AA01" 2  20 1 "mg" 
11 "C10AA01" 3  20 1 "mg" 
11 "M05BA04" 1  10 1 "mg" 
11 "M05BA04" 2  10 1 "mg" 
11 "M05BA04" 3  10 1 "mg" 
11 "N02AX02" 1  50 1 "mg" 
11 "N02BE01" 1 500 6 "mg" 
11 "N02BE01" 2 500 8 "mg" 
11 "N02BE01" 3 500 8 "mg" 
11 "N06AB06" 1  50 1 "mg" 
11 "N06AB06" 2  50 1 "mg" 
11 "N06AB06" 3  50 1 "mg" 
11 "N06AX11" 1  30 1 "mg" 
11 "N06AX11" 2  30 1 "mg" 
11 "N06AX11" 3  30 1 "mg" 
end
reshape wide dosis dosis_antal dosis_enhed, i(id m_kode) j(wave)
list, clean noobs

. list, clean noobs

    id    m_kode   dosis1   dosis~l1   dosis~d1   dosis2   dosis~l2   dosis~d2   dosis3   dosis~l3   dosis~d3  
    11   A02BC02       40          1         mg       40          1         mg       40          1         mg  
    11      A12A        1          2        stk        1          2        stk        1          2        stk  
    11   B01AC06       75          1         mg       75          1         mg       75          1         mg  
    11   C10AA01       20          1         mg       20          1         mg       20          1         mg  
    11   M05BA04       10          1         mg       10          1         mg       10          1         mg  
    11   N02AX02       50          1         mg        .          .                   .          .             
    11   N02BE01      500          6         mg      500          8         mg      500          8         mg  
    11   N06AB06       50          1         mg       50          1         mg       50          1         mg  
    11   N06AX11       30          1         mg       30          1         mg       30          1         mg

[William Lisowski]

Bonus answer. Here's an approach that doesn't use reshape wide. The trick is to use the fillin command so that each patient/drug has a record for each wave. Note that I adjusted your data to get a few different situations.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id str7 m_kode byte wave int dosis byte dosis_antal str3 dosis_enhed
11 "A02BC02" 1  40 1 "mg" 
11 "A02BC02" 2  40 1 "mg" 
11 "A02BC02" 3  40 1 "mg" 
11 "A12A"    1   1 2 "stk"
11 "A12A"    2   1 2 "stk"
11 "A12A"    3   1 2 "stk"
11 "B01AC06" 1  75 1 "mg" 
11 "B01AC06" 2  75 1 "mg" 
11 "B01AC06" 3  75 1 "mg" 
11 "C10AA01" 1  20 1 "mg" 
11 "C10AA01" 2  20 1 "mg" 
11 "C10AA01" 3  20 1 "mg" 
11 "M05BA04" 1  10 1 "mg" 
11 "M05BA04" 2  10 1 "mg" 
11 "M05BA04" 3  10 1 "mg" 
11 "N02AX02" 1  50 1 "mg"  
11 "N02BE01" 1 500 6 "mg" 
11 "N02BE01" 2 500 8 "mg" 
11 "N02BE01" 3 500 8 "mg" 
11 "YYYY"    2  50 1 "mg" 
11 "YYYY"    3  50 1 "mg" 
11 "ZZZZ"    1  30 1 "mg" 
11 "ZZZZ"    3  30 1 "mg" 
end
fillin id m_kode wave
replace dosis = 0 if _fillin==1
drop _fillin
bysort id m_kode (wave): generate started = wave>1 & dosis>0   & dosis[_n-1]==0
bysort id m_kode (wave): generate stopped = wave>1 & dosis==0 & dosis[_n-1]>0
list, sepby(id m_kode) noobs

. list, sepby(id m_kode) noobs

  +-----------------------------------------------------------------------+
  | id    m_kode   wave   dosis   dosis_~l   dosis_~d   started   stopped |
  |-----------------------------------------------------------------------|
  | 11   A02BC02      1      40          1         mg         0         0 |
  | 11   A02BC02      2      40          1         mg         0         0 |
  | 11   A02BC02      3      40          1         mg         0         0 |
  |-----------------------------------------------------------------------|
  | 11      A12A      1       1          2        stk         0         0 |
  | 11      A12A      2       1          2        stk         0         0 |
  | 11      A12A      3       1          2        stk         0         0 |
  |-----------------------------------------------------------------------|
  | 11   B01AC06      1      75          1         mg         0         0 |
  | 11   B01AC06      2      75          1         mg         0         0 |
  | 11   B01AC06      3      75          1         mg         0         0 |
  |-----------------------------------------------------------------------|
  | 11   C10AA01      1      20          1         mg         0         0 |
  | 11   C10AA01      2      20          1         mg         0         0 |
  | 11   C10AA01      3      20          1         mg         0         0 |
  |-----------------------------------------------------------------------|
  | 11   M05BA04      1      10          1         mg         0         0 |
  | 11   M05BA04      2      10          1         mg         0         0 |
  | 11   M05BA04      3      10          1         mg         0         0 |
  |-----------------------------------------------------------------------|
  | 11   N02AX02      1      50          1         mg         0         0 |
  | 11   N02AX02      2       0          .                    0         1 |
  | 11   N02AX02      3       0          .                    0         0 |
  |-----------------------------------------------------------------------|
  | 11   N02BE01      1     500          6         mg         0         0 |
  | 11   N02BE01      2     500          8         mg         0         0 |
  | 11   N02BE01      3     500          8         mg         0         0 |
  |-----------------------------------------------------------------------|
  | 11      YYYY      1       0          .                    0         0 |
  | 11      YYYY      2      50          1         mg         1         0 |
  | 11      YYYY      3      50          1         mg         0         0 |
  |-----------------------------------------------------------------------|
  | 11      ZZZZ      1      30          1         mg         0         0 |
  | 11      ZZZZ      2       0          .                    0         1 |
  | 11      ZZZZ      3      30          1         mg         1         0 |
  +-----------------------------------------------------------------------+

[Natallia Lapitskaya]

Dear William, thanks so much for your suggestions. You understand it completely right: "Like, what would you want if the patient got a medication at baseline, but not midway, but again at followup, at a different dosage than at baseline?"- that is what I want to answer. I will work your approaches this weekend and see if I get get it work in my Stata.
Thanks so much and nice weekend! Natallia