Merging Excel Files to Create a Stata Dataset

[Al Kindi]

Hello all,
New here, new to statistics, programming, and brand new to Stata so bear with me.
I am trying to merge three separate Excel files (Location: "C:\Stata") into a single Stata dataset ('MERGED') for analyses.
File names:
AE.xls
FZ.xls
WEIGHTS.xls
Unique Identifier (in all three): ID

What would be the code to go about creating this new data set? Thank you so much in advance.

[daniel klein]

In general, see

help import excel
help merge

If you are planning to stick with Stata for a while, read those help files, then start step by step. Something like

import excel using C:\Stata\AE.xls , clear
save AE.dta
import excel using C:\Stata\AE.xls , clear
save FZ.dta
import excel using C:\Stata\WEIGHTS.xls , clear

merge 1:1 ID using AE , generate(_mergeAE)
merge 1:1 ID using FZ , generate(_mergeFZ)

save MERGED.dta

A canned alternative is xls2dta (from SSC, see: help ssc). If those three files are the only Excel files in the directory

xls2dta , clear : merge 1:1 ID using C:\Stata\*.xls
save MERGED.dta

If you are interested in the underlying technique, search for similar posts on the forum and see

help foreach
help extended fcn

Best
Daniel

[Al Kindi]

Daniel, thank you so much for a prompt reply! Just to clarify, should I be doing just the steps you listed or something like this (because there are three total files);
import excel using C:\Stata\AE.xls , clear save AE.dta import excel using C:\Stata\FZ.xls , clear save FZ.dta import excel using C:\Stata\WEIGHTS.xls , clear save WEIGHTS.dta merge 1:1 ID using AE , generate(_mergeAE) merge 1:1 ID using FZ , generate(_mergeFZ) merge 1:1 ID using WEIGHTS , generate(_WEIGHTS) save MERGED.dta I will give this a shot and check should I need further help. Thanks!

[Al Kindi]

import excel using C:\Stata\AE.xls , clear
save AE.dta
import excel using C:\Stata\FZ.xls , clear
save FZ.dta
import excel using C:\Stata\WEIGHTS.xls , clear
save WEIGHTS.dta

merge 1:1 ID using AE , generate(_mergeAE)
merge 1:1 ID using FZ , generate(_mergeFZ)
merge 1:1 ID using WEIGHTS , generate(_WEIGHTS)

save MERGED.dta

[daniel klein]

Saving the last dataset, that is WEIGHTS, is not necessary; you are using this last imported dataset as your master dataset to which the other two are then merged. Likewise, the last merge command is not necessary because you start out with WEIGHTS in memory.

Best
Daniel

[Al Kindi]

Sounds great! Thank you so much!

[Mahinda Sene]

Can you let me know how these codes are modified if we have UNBALANCED Panel data (for example with Unique Identifier and years 2008 - 2012)