Replacing ID on different data

[Dew FR]

Hello,

I am having 2 different data file from excel. This is data A (general data)

. list id photo_2011 photo_folup sex age dm_type

     +----------------------------------------------------+
     | id   photo_2011   photo_f~p   sex    age   dm_type |
     |----------------------------------------------------|
  1. |  1   15/08/2011   11-Jul-16     1   66.6         . |
  2. |  1   01/06/2011   26-Jun-15     0     63         2 |
  3. |  1   09/08/2011   05-Jul-17     0   85.9         2 |
  4. |  1   19/07/2011   15-Dec-15     0   78.3         . |
  5. |  1   18/11/2011   25-May-17     0   64.8         . |
     |----------------------------------------------------|
  6. |  1   24/06/2011   01-Dec-15     1   82.2         2 |
  7. |  1   18/04/2011   26-May-16     0   67.1         2 |
  8. |  1   02/02/2011   31-Oct-16     0   67.7         . |
  9. |  1   05/04/2011                 0   69.9         2 |
 10. |  2   15/04/2011   31-May-16     1   68.8         2 |
     |----------------------------------------------------|
 11. |  2   25/05/2011   13-Sep-16     1   70.1         2 |
 12. |  2   18/01/2011   18-Feb-16     0   69.6         2 |
 13. |  3   11/10/2011   01-Dec-15     1   70.1         2 |
 14. |  3   19/04/2011   18-Mar-16     1   69.2         2 |
 15. |  3   13/05/2011   03-Jun-16     0   67.5         2 |
     |----------------------------------------------------|
 16. |  3   18/11/2011   15-Mar-16     0   65.2         . |
 17. |  4   07/04/2011   22-Mar-16     0     71         . |
 18. |  4   20/10/2011   24-Mar-17     0   71.1         2 |
 19. |  4   18/08/2011   09-Nov-16     1   67.9         2 |
 20. |  4   09/11/2011   23-Nov-16     1   65.3         2 |
     |----------------------------------------------------|
 21. |  4   27/10/2011   07-Jan-16     0     65         2 |
 22. |  4   12/04/2011   25-Apr-16     1   66.1         2 |
 23. |  4   01/02/2011   12-Feb-16     0   69.8         . |
 24. |  4   11/07/2011   09-Aug-16     0     66         2 |
 25. |  4   12/01/2011   31-Jan-17     0   70.4         2 |
     |----------------------------------------------------|
 26. |  4   03/08/2011   08-Feb-16     0     68         2 |
     +----------------------------------------------------+

and this is data B (medication data)

. list id dm_med med_date

     +----------------------------------------------------------------+
     |   id                                       dm_med     med_date |
     |----------------------------------------------------------------|
  1. | 7287     Metformin Hydrochloride  Tablets  500 mg   21/12/2011 |
  2. | 7287   Pioglitazone Hydrochloride  Tablets  15 mg   21/12/2011 |
  3. | 7287                   Repaglinide  Tablets  2 mg   21/12/2011 |
  4. | 7287                      Metformin 500mg tablets   28/09/2015 |
  5. | 5682                      Metformin 500mg tablets   01/10/2015 |
     |----------------------------------------------------------------|
  6. | 5682                      Metformin 500mg tablets   27/10/2015 |
  7. | 3729                      Metformin 500mg tablets   20/11/2015 |
  8. | 3729     Metformin Hydrochloride  Tablets  500 mg   14/12/2015 |
  9. | 3729   Pioglitazone Hydrochloride  Tablets  15 mg   14/01/2016 |
 10. | 2983                   Repaglinide  Tablets  2 mg   28/09/2015 |
     |----------------------------------------------------------------|
 11. | 2983                      Metformin 500mg tablets   01/10/2015 |
 12. | 2983                      Metformin 500mg tablets   27/10/2015 |
 13. | 2983                      Metformin 500mg tablets   14/01/2016 |
 14. | 2983                      Metformin 500mg tablets   14/01/2016 |
     +----------------------------------------------------------------+

I want to merge these data in STATA. These two data have a different ID. There is another data that describe the ID (ID description). For example for this data, ID 7287 = 3 (person with ID 7287 in data B (medication data) is the same person with ID 3 in data A (general data)).

. list id_b id_a

     +-------------+
     | id_b   id_a |
     |-------------|
  1. | 3729      1 |
  2. | 5682      2 |
  3. | 7287      3 |
  4. | 2983      4 |
     +-------------+

It is impossible to replace one by one by using code . replace as it is more than 10.000 ID. Can you please help me how to replace ID in data B (medication data) based on ID description in a more efficient way?

[Andrew Musau]

You need to do two merges. First, merge the dataset with the id keys with either dataset, creating a new id variable and then merge with the other dataset. You need to rename the id variables so they are consistent prior to performing the merges.

[Dew FR]

Many thanks Andrew Musau ! It works perfectly!