I am currently trying to merge two datasets with a common "year" variable using Stata. One dataset is purely for incorporating CPI with an associated year, and so the only variables are "year" and "cpilevel". The other is our main dataset, and we are trying to merge the CPI-year dataset in order to create a new "cpilevel" variable in the main dataset associated with the same years. I am attaching photos of the CPI-specific dataset and a cross-section of the main dataset (there are far too many observations to capture the whole set). Please let me know the easiest way to merge these datasets; we have tried using m:1 codes but can't seem to get the right combination.

enter image description here

enter image description here


Here are the results of the merge command. It did not accomplish what we need in terms of merging the "cpilvl" variable with the "year" variable in each of the main observations, but it seems to be on the right track?

enter image description here

enter image description here

  • $\begingroup$ Are you looking for a solution with Stata or just any tool? $\endgroup$
    – Jonathan
    Commented Nov 16, 2019 at 20:42
  • $\begingroup$ @Sammy Stata specifically $\endgroup$ Commented Nov 16, 2019 at 23:25
  • $\begingroup$ See the docs: stata.com/manuals13/dmerge.pdf $\endgroup$
    – Peter
    Commented Nov 17, 2019 at 17:38

1 Answer 1

  1. Have a file open in your current stata session. in your case let's say the file opened is M&D Research Project one.

  2. On the top option bar mentioning 'File', 'Edit' etc. go to 'Data' -> 'Combine Datasets' -> 'Merge two Datasets'

  3. In Type of merges choose 'Many-to-One' since your currently open dataset is 'M&D' having multiple year observations that have to merge with one observation per year in your 'cpi' dataset.

  4. In 'Key Variables' mention the name of the column to match/merge the data with. In your case it's 'year'.

  5. Choose the file 'cpi' under 'Filename of dataset on disk'.

You'll have your resultant data.

NOTE: You may have to make sure the order of the year on both datasets is sorted

Also if you want to merger cpi with M&D and not vice versa, open cpi dataset first and choose many to one relation. The rest of the steps are same

  • $\begingroup$ Hi Rishabh, thanks for your suggestion. If you look at the post, I've edited it to include the attempt. Any further suggestions on how to get the result I'm looking for here? $\endgroup$ Commented Nov 17, 2019 at 17:39
  • $\begingroup$ @deaconlenny Hi deaconlenny, I am new to strata so sorry for suggesting wrong merge, you can try changing the merge value from m:1 to 1:m or 1:1 see if any of that works. If that doesn't maybe the datatypes of year in both cases are different $\endgroup$ Commented Nov 18, 2019 at 7:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.