Re: st: Multiple Sequential Merges

[Maarten Buis <maartenlbuis@gmail.com>]

--- On Wed, Apr 3, 2013 at 7:36 AM, Lisa Wang wrote:
> I am unsure as to whether I am applying the merge function correctly
> or not. I would like to merge values from multiple spreadsheets/.dta
> files into one master sheet "master". Here is the code I have at the
> moment:
>
> merge 1:1 ID using "C:\Users\Admin\Documents\Data1.dta", force
>
> All good as it will merge all the data into my "master" and then put .
> (missing values) for all the observations where it is not matched.
> However, if I then run:
>
> merge 1:1 ID using "C:\Users\Admin\Documents\Data2.dta", force
>
> then the values from Data2 do not appear in my "master" file even
> though the results say it has been merged (i.e. still appears as . )
>
> What I want to achieve is similar to a VLOOKUP in Excel over multiple
> sheets but I thought I would just do a merge in STATA instead.
>
> Data1, Data2, Data3...files all have the same columns as each other if
> that is any assistance.

I don't understand your question. Is your problem reproduced in the
example below?

*------------------ begin example ------------------
clear all

// create some example files
tempfile master a b

input id
1
2
3
4
end

save `master'

drop _all
input id x
1 10
2 30
end
save `a'

drop _all
input id y
3 15
4 25
end
save `b'

// do the merging
// and use -list- to display the results
use `master'
merge 1:1 id using `a'
list
drop _merge

merge 1:1 id using `b'
list
*------------------- end example -------------------
* (For more on examples I sent to the Statalist see:
* http://www.maartenbuis.nl/example_faq )

Do you need to specify the -force- option? If you do, then that is a
sign that you need to do some extra data preparation before merging.
Merging files is dangerous. When doing some quick work to see if
something is feasible then using a convenience options like -force-
might be justified, but if you want to seriously analyse the resulting
dataset than you should never use it and always do the necessary data
preparation yourself.

More practical tips on mergins can be found here:
<http://blog.stata.com/2011/04/18/merging-data-part-1-merges-gone-bad/>
and
<http://blog.stata.com/2011/05/27/merging-data-part-2-multiple-key-merges/>

The program we are using is called Stata not STATA, this is explained
in the Statalist FAQ.

-- Maarten

---------------------------------
Maarten L. Buis
WZB
Reichpietschufer 50
10785 Berlin
Germany

http://www.maartenbuis.nl
---------------------------------
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/