Merging Multiple file in Stata

[Kazi Aiman Udoy]

Hello Everyone,

I want to merge three files using macros and foreach loop. The file names are 1.dta, 2.dta, and 3.dta. Each file has two variables. 1.dta has id and age. 2.dta has id and edu. 3.dta has id and state. Taking id as a common variable I want to merge these three and want to generate a new dta with id, age, edu and state. The code I followed is the following one:

cd "F:\UDOY\Graduation\ECON 7th Semester_UDOY\Research Methodology\RM Class Lectures\NH Stata\Merging multiple files"
use "F:\UDOY\Graduation\ECON 7th Semester_UDOY\Research Methodology\RM Class Lectures\NH Stata\Merging multiple files\1.dta"
local files : dir "F:\UDOY\Graduation\ECON 7th Semester_UDOY\Research Methodology\RM Class Lectures\NH Stata\Merging multiple files" files "*.dta"

foreach file in `files' {
merge id using `file'
}

after running the code it shows "no; data in memory would be lost" in red mark

and if a br the data a variable has been created named as _merge which contain matched(3) in each row.

But I didn't get the expected result.

What should I do now?

Thank You

[George Ford]

With 3 files it's hardly worth the trouble.

use 1.dta, clear
joinby id using 2.dta , unmatched(master) _merge(_merge_2)
joinby id using 3.dta, unmatched(master) _merge(_merge_3)

[Kazi Aiman Udoy]

George Ford Thanks for the help. It worked. But I don't want it to be done in this way. My instructor mentioned to use local or global macros and foreach loop.

How can do that using local or global and foreach loop?

That would be greatly helpfull for me

Thank You

[Nick Cox]

Show your instructor this thread with a comment from experienced users that doing it simply and directly beats using unnecessary syntax for its own sake. I will take a Fail grade on the chin.

[Hemanshu Kumar]

As noted earlier, using macros for this seems overkill just for three files. I will however assume that the instructor has created this as a toy example for you to learn a technique that will be useful later in more complex scenarios.

To that end, I would suggest looking at the help for the syntax of the merge command (you are using an outdated syntax). You will probably want something like

foreach file of local files {
    merge 1:1 id using `file', nogen
}

assuming all the files have a single observation per id. The nogen is to ensure it does not create the _merge variable. If you do want this variable, you will need to give it a different name each time, otherwise you will get another error related to you trying to create a new variable of the same name as an existing one (in the second iteration of your loop). So you might want something like:

local i = 1
foreach file of local files {
    merge 1:1 id using `file', gen(_merge`i')
    local i++
}

[George Ford]

Once you cd, you don't need to include the directory info.

clear
cd "S:/STATA/Junk"
forv i = 1/3 {
clear
set obs 100
g id = _n
g x`i' = rnormal()
save `i', replace
}

use 1.dta, clear
local files : dir . files "*.dta"
foreach file  in `files' {
    joinby id using `file'
}

[Nick Cox]

This thread seems to overlap with one started earlier https://www.statalist.org/forums/for...d-foreach-loop