How to merge datasets while maintaining a year distinction?

[Daniel Moore]

Hello all,

I am trying to merge a set of 11 data sets from years 2002-2012. Each file is contained in the following path: C:\Users\Daniel\Desktop\RA\Landmines\Daniel\CDB\me rge

The files are named in the following manner: raw_`year'.xlsx. I am wanting to merge only the sheets in their respective files called "C_`year'_E(1)". I want to merge them into one panel. However, the year values themselves are not contained within the file, but only the file name itself. All files contain the same variables and identifiers.

I am only familiar with merging two files at a time and have not constructed a multi-year panel before, and so I am struggling with this quite a bit. Any tips would be appreciated.

Thanks!

[Clyde Schechter]

Well, if you merge these files you won't end up with a panel. You'll end up with an unwieldy wide-layout data set that will be very difficult to do anything useful with.

You want to -append- them. The following untested code gives the general approach:

clear*
tempfile building
save `building', emptyok
forvalues y = 2002/2012 {
    import excel using raw_`y'.xlsx, clear sheet("C_`y'_E(1)"")
    gen year = `y'
    append using `building'
    save `"`building'"', replace
}

Beware of typos or other errors--but this is the logic.

[Daniel Moore]

Well, if you merge these files you won't end up with a panel. You'll end up with an unwieldy wide-layout data set that will be very difficult to do anything useful with.

You want to -append- them. The following untested code gives the general approach:

clear*
tempfile building
save `building', emptyok
forvalues y = 2002/2012 {
import excel using raw_`y'.xlsx, clear sheet("C_`y'_E(1)"")
gen year = `y'
append using `building'
save `"`building'"', replace
}

Beware of typos or other errors--but this is the logic.

Thank you very much! This lead me right where I needed to go.