How to use local when there are too many listed names?

[Giang Dao]

I am using "local" in STATA to list out all the names I'm gonna use in the loop that followed. The code will be very long because I have so many names. All these names are in an Excel file and I want to load them after local command, to save time. How can I do that?

local LetterList A B C D E F...... /// (too long)
foreach Letter of local LetterList {...}

And the Excel file is like this:

Letter	X	Y
A	...	...
B
C
D
...

I read from Stata Help that I can use "macro" command with "adosubdir". Is it right?

[Baptiste Ottino]

Ok, by "too long", you mean that the list is too long for you to type by hand, but not that it is too long to fit in a macro (264392 chars in Stata/IC), right? So the question in my understanding is, how do I put a list of names from an Excel file in a local macro.

Suppose your names are in an excel file "letters.xlsx", under sheet "sheet" and on the range A1:A6. Start by preserving whatever dataset you're working on, and open your letters as a dataset

preserve
clear
import excel using "test.xlsx", sheet("sheet") cellrange(A1:A6)

In my example, the result would look like this

* Example generated by -dataex-. To install: ssc install dataex
clear
input str1 A
"A"
"B"
"C"
"D"
"E"
"F"
end

The variable is called "A" because it was on the Ath column in the excel file. Next, you will want to save your variable A as a local macro using -levelsof-, and restore your initial dataset. After that, you can use the list to do anything.

* Saves the list of distinct names in a local macro
 levelsof A, local name_list

* Restores
restore

* Profit
foreach letter of local name_list {
    di "`letter'"
}

Here, I assumed that all your names are different (I used levelsof), and that they are either in alphabetical order, or you don't care about the order of execution of the loop. If any of these assumptions, you need to find an alternative to levelsof. But the method of loading as a dataset is likely the best here.

[daniel klein]

Seems like trying to loop over observations. What is the ultimate goal here?

Best
Daniel

[Giang Dao]

@Baptiste Ottino: Quite helps. Thanks a lot! The number of letters in my case is 200, so it's time consuming to type them out.
@daniel klein: I want to merge many Excel sheets into 1.

[daniel klein]

@daniel klein: I want to merge many Excel sheets into 1.

I have a hard time to see why this would require a loop over observations. Merging many (or all) sheets in one Excel file is automated in my xls2dta (SSC) but I cannot say whether it can be easily applied to your case based on the little information here. If Baptiste's suggestions is sufficient for you that is fine; please do not bother providing more details in this case.

Best
Daniel

[Giang Dao]

daniel klein Thanks for your comment! Actually I have to merges the same sheet of many excel files. unfortunately they are not exactly in the same format. The beginning variables are different, which is why Baptiste's answer helped here.

[Giang Dao]

Baptiste Ottino Actually I can identify the first variable form the code of Baptiste, but i still could not write a code with meaning "If this first variable == "vsdfgr"". I tried "If `first' ==..." but it didn't work. sorry and thanks for your help!

[Giang Dao]

Ah it works now.