Converting an Excel file into a dta. (stata) file

Question

I want to merge one using file (which is an Excel file) onto the masterfile in Stata. Might be easy but I don't know how to save the Excel file into a dta file so that I am, in turn, able to merge the two data sets in Stata.

I have read about the xls2dta command but I'm not sure if I'm looking in the right direction.

Answer 1

You do not need to convert the file with a special command like xls2dta. You just import it to Stata and save it in a temporary file in dta format and then you can merge that temporary file with your dta file.

Here is a reproducible example. All you need to do to run this code is to update the folder path in the local in the beginning of the code. You should be able to adapt the code in the "Solution" section to your code.

*Set this path to an example folder on your computer
local folder "C:\Users\username\myfolder"

******************************************************************
*Prepare Excel example file
******************************************************************

*Use built in example data
sysuse auto, clear
*Rename all files but ID var make
foreach var of varlist _all {
    if "`var'"!="make" rename `var' `var'_ex
}
*save in excel format
export excel "`folder'/autoexcel.xls", firstrow(variables) replace

******************************************************************
*Solution
******************************************************************

*Use built in example data
sysuse auto, clear

*Load the excelfile into a temporary .dta file
preserve
    import excel "`folder'/autoexcel.xls", firstrow clear
    tempfile autoexcel_tmp
    save `autoexcel_tmp'
restore

*Merge the dta file in memory with tempfile of excel file in dta format
merge 1:1 make using `autoexcel_tmp'