Ambigious Abbreviation Error in a Loop

[Muhammad Saleh]

Hi all with apologies, I am trying to import almost 300 excel sheets stored in a folder, clean each one of them and append as a one large stata .dta file. Here below is my code :

clear
tempfile building
save `building', emptyok

local filenames: dir "C:\Country profiles" files "*.xlsx*"
local x B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM
local y y1985 y1986 y1987 y1988 y1989 y1990 y1991 y1992 y1993 y1994 y1995 y1996 y1997 y1998 y1999 y2000 y2001 y2002 y2003 y2004 y2005 y2006 y2007 y2008 y2009 y2010 y2011 y2012 y2013 y2014 y2015 y2016 y2017 y2018 y2019 y2020 y2021 y2022

foreach f of local filenames {
clear
import excel using `"`f'"', sheet("inflow-M")
gen g=A if A==A[1]
replace g=g[_n-1] if g==""
rename g country
keep if B!=.
drop AN AO
drop if A=="Reporting Country"
rename (A) (source)

foreach var of varlist `x' {
gettoken new y : y
rename `var' `new'
replace source = subinstr(source, ",","", .) //to remove special chracters
replace source = subinstr(source, " ","", .) //to remove spaces from strings
sxpose2, clear firstnames varname force
gen g=UnitedStates if UnitedStates==UnitedStates[39]
replace g=g[_N] if g==""
drop if _varname=="country"
rename g country
rename _varname year
drop year
gen year = 1985+_n-1
gen source = `"`f'"'
display `"Appending `f'"'
append using `building'
save `"`building'"', replace

}

}

After running the very first file, STATA produces an error saying C ambegious abbreviation. Would be grateful for guidance/correction please

[Daniel Feenberg]

Stata will tell you the line in a loop generating the error only in trace mode:

set trace on
...

Once you know the problematic line, the error should be obvious. Or you could jsut turn off abbreviations:

set varabbrev off

which might be a better approach. Unless you need the abreviation facility, tuning it off in the stata profile is
perhaps wise.

[Muhammad Saleh]

Dear Daneil, truly appreciate your guidance it really helped me. Kind regards