I am using Stata 16.1 MP to merge six .csv files on neonatal, infant, and child mortality. I would like to create a ADM1 year panel data set that contains probability and count measures for neonatal, infant, and child mortality.

I have used the following code which successfully creates the .dta files and then merges the files. However, when I do the merge there are 31,500 observations and 14 variables. The study population is correct (N=31,500), but the resulting data set should contain more variables. I think the problem is that the variable names are the same for each of the data sets and therefore I'm not merging in the new information (child and neonatal mortality).

Is it possible to rename the variables within the macro so the variables will successfully merge at the ADM1 and year level?


Code:
local myfilelist : dir . files "*.csv"
foreach file of local myfilelist {
drop _all
insheet using "`file'"
local outfile = subinstr("`file'",".csv","",.)
save "`outfile'", replace
}


local myfilelist : dir . files "*.dta"
use ihme_lmics_u5m_2000_2017_d_infant_adm1_y2019m10d16

foreach f of local myfilelist {
merge 1:1 adm1_code year using `f'
drop _merge
save master2, replace
}
In order to provide a sample of the data I appended the data sets and pasted the sample below.


Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input str32 adm0_name int adm0_code str43 adm1_name long adm1_code int year byte age_group_id str8 age_group_name byte sex_id str4 sex byte measure_id str20 measure float(mean lower upper)
"Afghanistan" 2 "Badakhshan" 1002 2003 28 "Infant"   3 "Both" 27 "Probability of death"  .11054444  .09099275 .13221398
"Afghanistan" 2 "Badakhshan" 1002 2003  1 "Under 5"  3 "Both" 27 "Probability of death"   .1530938  .12572843 .18161544
"Afghanistan" 2 "Badakhshan" 1002 2010 28 "Infant"   3 "Both" 27 "Probability of death"   .0794504  .06524893  .0949191
"Afghanistan" 2 "Badakhshan" 1002 2010  1 "Under 5"  3 "Both"  1 "Deaths"                 6222.563   4976.263  7709.781
"Afghanistan" 2 "Badakhshan" 1002 2007 28 "Infant"   3 "Both" 27 "Probability of death"  .08842167  .07211453 .10599828
"Afghanistan" 2 "Badakhshan" 1002 2017  1 "Under 5"  3 "Both" 27 "Probability of death"  .08431107  .06200083 .10956819
"Afghanistan" 2 "Badakhshan" 1002 2005 28 "Infant"   3 "Both" 27 "Probability of death"  .09914187  .08018186 .11967482
"Afghanistan" 2 "Badakhshan" 1002 2016 42 "Neonatal" 3 "Both" 27 "Probability of death" .034245655 .025212256 .04473999
"Afghanistan" 2 "Badakhshan" 1002 2004 28 "Infant"   3 "Both" 27 "Probability of death"  .10478496  .08530268 .12553692
"Afghanistan" 2 "Badakhshan" 1002 2017 28 "Infant"   3 "Both"  1 "Deaths"                 4840.812  3561.0266  6388.955
"Afghanistan" 2 "Badakhshan" 1002 2013 28 "Infant"   3 "Both"  1 "Deaths"                 4897.643   3793.688  6178.554

end