Merge variables in excel files by pairing _id

[Vidou Riddhy]

Hello, stata community!

I'd like to ask if it's there any way(like using loop) to merge variables from excel files in a folder into one excel file. there are 2 conditions. Between excel files, if _id doesn't match => take it to a new file. If _id matches, merge or gather those into 1 excel file.
Thank in advance!

[Ali Atia]

I'm not quite sure what you mean by "if _id doesn't match" -- my assumption is that it means some Excel files in a directory do contain a variable called _id, while others don't. I also assume that, for all files with an _id variable, that variable is a unique identifier (e.g. does not repeat across observations). Any files with an _id which is not a unique identifier are excluded from the merge(s).

Here is some code which will import all Excel files one by one, merge those with a uniquely identifying _id variable, and export an Excel file containing the results.

clear all
cd "<YOUR PATH HERE>"
local files: dir . files "*.xlsx"

local i = 0
foreach file of local files{
    import excel "`file'", clear firstrow
    capture confirm variable _id
    if !_rc{
        capture isid _id
        if !_rc{
            local ++i
            if `i' == 1 tempfile wanted
            else merge 1:1 _id using `wanted', nogen
            save `wanted', replace
        }
    }
}

use `wanted', clear

export excel wanted.xlsx, firstrow(variables) replace

[Vidou Riddhy]

Hello Ali Atia! Thank you so much for your effort! And also sorry for my bad english! It means that _id of the observation may or may not in those excel file. so if 2 excel files contains of the same _id, I want it to be in a new excel file with other variables.