Merge One Variable to Multiple Instances in Master Dataset

[Lauren Dula]

Hello,

I have a dataset in which multiple organizations can serve the same county. I have a crosswalk by county FIPS codes that looks like this:

ORG_ID	FIPS
1001	1095
1001	1055
1003	1123
1003	1095

So as you can see, Org 1001 takes in counties 1095 and 1055. However, Org ID 1003 also takes in county 1095. I want to merge county-level data into this by FIPS, so variables such as County Median Income, and Population. So I want County 1095 data to populate both rows that have 1095 FIPS codes. I cannot get rid of duplicates bc the duplicates are there intentionally, as these data will eventually be condensed down to Org Service Region level data (just one observation for ID 1001, 1003, etc.). Is there a way to make this work without deleting duplicates?

Thank you,
Lauren

[Sarah Edgington]

Please check the FAQ for information on details you should provide to make your questions easy to answer.

You don't say what your county-level data looks like so I'm sort of forced to guess. Assuming that your county-level data has only one observation per county this should be a straight forward many-to-one merge. If that is the case then something like this should work (starting with the organization data as your master data).

merge m:1 FIPS using "county_datafile"

If your county data has more than one observation per county--for example multiple years of data--you'll have to provide more information about what the data looks like and what you want it to look like after the merge. In that sort of situation you would most likely want to use joinby but it's hard to offer more advice than that without further information.