Merging multiple files in a loop

[Ritika Khurana]

Hi,

I am trying to merge 12 files with similar files name. Each file just has one variable.
I was following this thread (similar to what I want): https://www.statalist.org/forums/for...=1552507542959

Basing on that I ran my code but I get an error message: II2.dta not found.

The code that I ran was:

use "C:\Users\rk0022\Desktop\Env Eco\II1.dta", clear
forval i = 2/12 {
 merge 1:1 country year using II`i', nogen
 drop _merge
 }

Also, I tried this code:

use "C:\Users\rk0022\Desktop\Env Eco\II1.dta", clear
local files II2 II2 II4 II5 II5 II6 II7 II8 II9 II10 II11 II12
local vars  var2 var3 var4 var5 var6 var7 var8 var8 var9 var10 var11 var12
forval i = 2/12 {
 local f : word `i' of `files'
 local v : word `i' of `vars'
 merge 1:1 country year using `files', keepusing(`v')
 drop _merge
 }

even with this I found an error.


Could you please tell me where am I going wrong?

Thank you.

Ritika

[Mike Lacy]

You want `f', not `files' in the merge statement, since the local "files" contains the entire list of files. And, it's safer to include quotation marks around the file names, as well as the "dta" suffix, which I would presume your files (II2.dta, etc.) have. Notice also that your file list has II2 twice in a row.
Your merge should be:

merge 1:1 country year using "`f'.dta'", keepusing(`v')

[Sarah Edgington]

You probably need to specify the path to the file.
Try

use "C:\Users\rk0022\Desktop\Env Eco\II1.dta", clear
forval i = 2/12 {  
   merge 1:1 country year using "C:\Users\rk0022\Desktop\Env Eco\II`i'", nogen  
   drop _merge  
}

[Ritika Khurana]

Mike Lacy Thank you correcting that error. But it doesn't work still. Now the error that I get is II3.dta not found.

The code that I used was:

use "C:\Users\rk0022\Desktop\Env Eco\II1.dta", clear
local files II2 II3 II4 II5 II5 II6 II7 II8 II9 II10 II11 II12
local vars var2 var3 var4 var5 var6 var7 var8 var9 var10 var11 var12
forval i = 2/12 {
 local f : word `i' of `files'
 local v : word `i' of `vars'
 merge 1:1 country year using "`f'.dta", keepusing(`v')
 drop _merge
 }

Sarah Edgington it fixed my problem.

Thank you Sarah and Mike for your feedback on my code. I could atleast work around the code.

[Shahida Pervin]

cd " "

local files `allid' `Cutil' `Fissets' `Fuel' `Orhsts' `Otcome' `Output' `Rawerials' `salges' `Stock' `tat'
use "Fissets"
foreach x of local files {
merge 1:1 dpc using "`f'.dta", nogen
}
Hey, when I use this command I find my file is only the Fissets, it doesn't merge. Could you please help to merge data from multiple files? Thank you.

[Nick Cox]

econ stat https://www.statalist.org/forums/help#realnames explains our strong preference for real names here. Please read and act on it.

Otherwise you are using local macros here for your other filenames. Inserting the command

macro list

before your loop will show which local macros are visible. My guess is that you defined your local macros in one place and are trying to access them in another. But "local" means what it says, and local macros are local to the space in which they are defined. That even includes different parts of a do-file editor window if you run code in chunks.

[Shahida Pervin]

Thanks Nick. I wanted to edit my profile name but did not find an edit option, wonder if I need re-register.


I am trying with the "macro list" command, it gives the following
_data: all derived vars with id" "Capacity util" "Fixed assets" "Fuel" "Orher costs" "Other income" "Output" "Raw materials" "salary and wages" "Stock" "tax vat ; and
S_FN: Fixed assets.dta


Still the following code does not work. the defined data files are in the SMI2012

dir "\SMI2012"
local data "all derived vars with id" "Capacity util" "Fixed assets" "Fuel" "Orher costs" "Other income" "Output" "Raw materials" "salary and wages" "Stock" "tax vat"
di "`data'"
macro list
foreach x of local data {
use `x', clear
sort dpc
save `x'}
use "Fixed assets", clear
local data "all derived vars with id" "Capacity util" "Fuel" "Orher costs" "Other income" "Output" "Raw materials" "salary and wages" "Stock" "tax vat"
foreach x of local data{
merge 1:1 dpc using `x', nogen
}


Thanks in advance

[Nick Cox]

What to do about changing names is explained at the link I gave.

You need “ “ around filenames with spaces and around your reference to local macro x. I can’t show left-hand single quotes with this phone.

With your code you need to be working in the same directory or folder as your files.

There may be other problems: your code is a little hard to read as you don’t use CODE delimiters (FAQ Advice #12).

[Nick Cox]

Elsewhere you posted this code

local dir "\SMI2012"
local data: dir "`dir'" files "*.dta"
foreach file of local data {
use `"`dir'/`file'", clear
sort dpc
save, replace
}
use "Fixed assets", clear
foreach file of local data {
merge 1:1 dpc using `file'
}

As already pointed out in this thread, you need double quotes around filenames containing spaces, so the merge command still looks wrong.

Also, your compound double quotes aren't matched.

local dir "\SMI2012"
local data: dir "`dir'" files "*.dta"

foreach file of local data {
    use `"`dir'/`file'"', clear
    sort dpc
    save, replace
}

use "Fixed assets", clear

foreach file of local data {
    merge 1:1 dpc using "`file'"
}

The loop sorting on dpc does no harm, but merge doesn't depend on sort order.

[Shahida Pervin]

dir "\SMI2012"
local data "all derived vars with id" "Capacity util" "Fixed assets" "Fuel" "Orher costs" "Other income" "Output" "Raw materials" "salary and wages" "Stock" "tax vat"
di "`data'"
macro list
foreach x of local data {
use "`x'", clear
sort dpc
save "`x'"}
use "Fixed assets", clear
local data "all derived vars with id" "Capacity util" "Fuel" "Orher costs" "Other income" "Output" "Raw materials" "salary and wages" "Stock" "tax vat"
foreach x of local data{
merge 1:1 dpc using "`x'", nogen
}

I hope the code is readable now. I am working in the same directory. I used left hand single quote. Thank you, I appreciate your support.

[Nick Cox]

So, you need to tell us if the code works and if it doesn't work precisely what is wrong. What error messages do you get and when?

Code like this is hard to debug. Manifestly, we are not sitting at your computer to see your files and nothing is easily reproducible by us. The need to be explicit and precise about what is happening is multiplied accordingly.

Also, you're reintroducing errors already fixed. Your save needs to be save, replace.

Filenames with spaces in are a royal pain. I never, ever willingly use any such filenames with Stata. I think you need also

local data `" "all derived vars with id" "Capacity util" "Fuel" "Orher costs" "Other income" "Output" "Raw materials" "salary and wages" "Stock" "tax vat" "'

as otherwise the outermost " " will get stripped.

[Shahida Pervin]

Hi Nick, I am glad, its now working. Thank you so much for your kind support.

[Nick Cox]

Good. Do use that CONTACT US button to request a name change, although it's entirely possible that StataCorp personnel won't get to fix that until later this week.

[Shahida Pervin]

dir "\SMI2012"
local data `" "all derived vars with id" "Capacity util" "Fixed assets" "Fuel" "Orher costs" "Other income" "Output" "Raw materials" "salary and wages" "Stock" "tax vat" "'
di `data'
macro list
foreach x of local data {
use "`x'", clear
sort dpc
save "`x'", replace
}
use "Fixed assets", clear
local data `" "all derived vars with id" "Capacity util" "Fuel" "Orher costs" "Other income" "Output" "Raw materials" "salary and wages" "Stock" "tax vat" "'
foreach x of local data{
merge m:m dpc using "`x'", nogen
}

foreach x of local data {
  2. use "`x'", clear
  3. sort dpc
  4. save "`x'", replace
  5. }
file all derived vars with id.dta not found
r(601);

Strangely, I dont see any problem but it desn't run but it was okay last time. I wonder if there is any unintended change I made or any issue with stata. Appreciate your time if your could take a look.

[Nick Cox]

merge m:m 

is never a good idea. See the manual. That's unlikely to be the immediate issue, but the implication that you're running the same code is blown sky high.

Note that dir doesn't mean cd.