Problem merging multiple files

[Sergio Paolinetti]

Hello everyone! This is my first time posting on this forum and I'm a beginner in Stata (currently working on Stata 14).
I have 252 files that I want to merge, which contain a unique variable named "Bancos". The filenames are similar because they contain monthly information.

For example: BM-Cart. Inmovilizada-01_06, BM-Cart. Inmovilizada-02_06, BM-Cart. Inmovilizada-03_06, BM-Cart. Inmovilizada-04_06.

The first numbers (01, 02, 03, 04) represent months and the second number represents years. The 252 files that I mentioned above are just for 2006 (06)

Each file has from 2 to 9 variables but every file contains the variable Bancos.

My final goal is to merge these 252 files into a single dataset. I read the manuals and some related topics posted before but I couldn't find the answer I was looking for.
I don't even know if it is possible to complete this task with a single command, loop, etc.

I supposed that the merge 1:1 is the one I should work with, so I tried with several commands:

local x 01 02 03 04 05 06 07 08 09 10 11 12
foreach i of local x {
    foreach j in "Ofic. y Trabaj." "Cuota Act.SB" "Cuota Créd.SB" "Cuota Pas.SB" "Cuota Capt.SB" "Cuota Patr.SB" "Créd.Dest.Bco." "Créd.Plazo Bco." ///
    "Créd.Act.Eco. Bco." "Cart. Inmovilizada" "Colocaciones Agrícolas" "Créd.MicroEmpres." "Créd.Hipotecarios" "Depósitos Banco" "Dep a Plazos" ///
    "Ind. Patrimonio" "Ind. Calidad de Activos" "Ind. Gestión" "Ind. Rentabilidad" "Ind. Liquidez" "Riesgo" {
        use "C:\Users\sergi\Documents\Trabajo\SUDEBAN\STATA\2007\BM-Ofic. y Trabaj.-01_06.dta", clear
        merge 1:1 Banco using "C:\Users\sergi\Documents\Trabajo\SUDEBAN\STATA\2007\BM-`j'-`i'_06.dta"
        }
}

Evidently, I must be doing something wrong, this is another command I tried (a little bit shorter)

local x 01 02 03 04 05 06 07 08 09 10 11 12
foreach i of local x {
    use "C:\Users\sergi\Documents\Trabajo\SUDEBAN\STATA\2007\BM-Ofic. y Trabaj.-`i'_06.dta", clear
    sort Banco
    merge 1:1 Banco using "C:\Users\sergi\Documents\Trabajo\SUDEBAN\STATA\2007\BM-Riesgo-`i'_06.dta"
    drop _merge
    save "C:\Users\sergi\Documents\Trabajo\SUDEBAN\STATA\merge2007\BM-Merge-`i'_06.dta"
}

In the last one, what I had was a successful merge of these two files "Ofic. y Trabaj." and "Riesgo" but it wasn't what I was looking for since the merge was carried out month by month and I had 12 new files. Each file had the variables from "Ofic. y Trabaj ." and "Riesgo" with the corresponding month (01, 02, 03, etc).

I don't know if I made myself clear or showcase what I did and what I want to do.

To sum up, I want to unify the 252 monthly files into one single dataset. If the solution is completely different from the two commands I just posted (which I think it will be), it will be very useful.

Thank you very much,
Sergio P.

[Jorrit Gosens]

With some assumptions, I think you will probable want to append rather than merge.

Toydata:

sysuse auto, clear
drop price mpg
save auto1, replace
sysuse auto, clear
drop length turn
save auto2, replace

Putting it together:

clear
save appended, emptyok 
local filelist: dir . files "*.dta"
foreach file of local filelist {
  use `"`file'"', clear
  gen source = `"`file'"'
  append using appended.dta
  save appended.dta, replace
}

Note: the line

local filelist: dir . files "*.dta"

creates a llist of all your filenames, so no need to create them in your double loop.

And note that I assume that you are trying to put together repeated values of similar sets of variables, which is where append will be of use.


Also note the advice in the FAQ on the provision of a data example with dataex: https://www.statalist.org/forums/help#stata
This prevents people from having to guess too much about what your data looks like, and is usually a lot better than a long decsription in words.

[Sergio Paolinetti]

Thank you very much for your help Jorrit. I will review and take into consideration your advice.