How to use file name in a loop and rename command

[Saleh Babazadeh]

Hi stata people, I need to use the name of the file to rename variables. I have multiple files that have common variable (mean). I need to rename mean to mean_filename to specify the variable. Here is my example:

clear
local myfilelist : dir . files"*.csv"
foreach file of local myfilelist {
drop _all
insheet using `file'
rename mean mean_filename

local outfile = subinstr("`file'",".csv","",.)
save "`outfile'", replace
}

As you could see,

rename mean mean_filename

would not work. I have tries "" and many other tricks.

Saleh

[daniel klein]

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

Best
Daniel

[Robert Picard]

Here's another way of doing this using filelist and runby, both from SSC. I think its easier to create a dataset of file names and use the standard Stata commands to build a list of file names, and in this case variable names to use. The following code shows how to do that, making sure that the new variable names are valid Stata names. Also, you do not want to overwrite existing data if the new variable name is a duplicate so I use isid to check that each variable name to use is unique.

clear all

filelist, norecur
keep if strpos(filename,".csv")
gen vname = subinstr(filename,".csv","",.)
replace vname = strtoname("mean_" + vname)
isid vname

With the results from running the code above still in memory, you can process each file using runby. runby will loop over each value of vname, replace the data in memory with the data from the corresponding observation, and then run the my_import_routine program. Since you do not want to accumulate results in this case, the last command drops all observations at the end:

program my_import_routine
  local mean_name = vname
  insheet using "`=filename'", clear
  rename mean `mean_name'
  save "`mean_name'.dta", replace
  drop _all
end
runby my_import_routine, by(vname) verbose

I do not know where you are going with this, in particular if you intend to combine the data. If so, you are probably better to keep it in a long layout. In that case, the following code would simply input each dataset and append them all in one pass:

program my_import_routine
  local mean_name = vname
  insheet using "`=filename'", clear
  gen source = "`mean_name'"
end
runby my_import_routine, by(vname) verbose

[Saleh Babazadeh]

Thank you Robert and Daniel. I learnt a bunch.