Weird merge request

[Collin James]

Hello,

I have two data sets that I am trying to merge and some data samples below. Data set one contains crrp_app_id and hh_member_id. The first variable is an application id, and the second variable is a household member id (there can be many household members on one application). All numbers changed due to data privacy. Data set two contains, for each CRRP_APP_ID a payment that was received and its date. What I would like to do is have a data set where for every crrp_app_id hh_member_id and ym_payment (the date variable), I have the payment dates in long form. So it would look like data set three (described below but made up). Can you help me get here? I have tried a m:m merge on crrp_app_id--what it does is takes the first crrp_app hh_member and attaches every ym_payment to it, but it does not do this for the other hh_members. Many thanks.

Data set one:

* Example generated by -dataex-. For more info, type help dataex
clear
input long(crrp_app_id hh_member_id)
11    33
12    34
12  2271
13    35
13    37
13    40
13    41
13    42
end

Data set two:
clear
input long crrp_app_id float ym_payment
12 732
12 733
12 734
12 735
12 736
12 737
12 738
12 739
end
format %tm ym_payment
[/CODE]



Data set 3 (objective): Imaging application 12 received payments each month from 2020m1 to 2020m9.
crrp_app_id hh_member_id ym_payment
12 34 2020m1
12 34 2020m2
12 34 2020m3
12 34 2020m4
12 34 2020m5
12 34 2020m6
12 34 2020m7
12 34 2020m8
12 34 2020m9
12 2271 2020m1
12 2271 2020m2
12 2271 2020m3
12 2271 2020m4
12 2271 2020m5
12 2271 2020m6
12 2271 2020m7
12 2271 2020m8
12 2271 2020m9

[Collin James]

Seems like I need to in some way expand the first data set to make an obs for every month year that I want (2020m1 to 2022m9) and then merge that ? But not sure how.

[Clyde Schechter]

As you have discovered, -merge m:m- is not the solution to your problem. It is almost never the solution to anybody's problem. Even the Stata documentation for -merge- warns people not to use -merge m:m-. That can't be emphasized too much or too often.

For what you want to do, -merge- is the wrong tool. Check out -help joinby-.

[Collin James]

Thanks so much!