How to loop over excel sheet and lists in Stata?

Question

I just started working with Stata and I couldn't figure out the following.

How can I loop over the lists of Excel sheets and the indices. This works fine now.

clear all
set more off

local mysheets 1996 2000 2003 2007 2008 2010 
local indices index1 index2 index3

foreach sheetname of local mysheets {

    import excel "C:\stata\Data.xls", sheet(`sheetname') firstrow clear

    foreach index of local indices{
       tobit theta index, ll(0) ul(1)
       outreg using "C:\stata\results.doc" , `append'
       local append "append"    
    }

}

Note that your locals are named differently: mysheets vs. mysheet — Brendan
– Brendan, Commented Jun 28, 2015 at 16:28
Also, you are using 'append' before you define it (note the incorrect use of '; I can't figure out how to get SO to allow me to use the proper backtick in this context). And it's not clear why you would define it in the first place (in this context, at least). — Brendan
– Brendan, Commented Jun 28, 2015 at 16:38
@Brendan Thank you. Fixed that and it still not working. As for append I found that in that way if the file is not created already it wouldn't append for the first time but would for the rest. — Fatima
– Fatima, Commented Jun 28, 2015 at 17:17
Oh I have to change sheetname to ` sheetname' and it works. — Fatima
– Fatima, Commented Jun 28, 2015 at 17:35

Brendan · Accepted Answer · 2015-06-29 00:25:28Z

3

Just to post as an answer (so the question doesn't appear unanswered), since it seems to be a simple coding error:

Make sure local macro names are consistent throughout (mysheet vs. mysheets)
Use local macro syntax for the argument of foreach (in this case, sheetname) inside of foreach loop

If using a local macro to define the append option of outreg, define it before the option is called

clear all
set more off
local mysheets 1996 2000 2003 2007 2008 2010 
local indices index1 index2 index3

foreach sheetname of local mysheets {
    import excel "C:\stata\Data.xls", sheet(`sheetname') firstrow clear
    foreach index of local indices {
       tobit theta `index', ll(0) ul(1)
       local append "append"    
       outreg using "C:\stata\results.doc" , `append'
    }
}

edited Jun 29, 2015 at 0:25

answered Jun 28, 2015 at 17:46

Brendan

4,0111 gold badge17 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Parfait Over a year ago

Might I add also Brendan to add the ticks on the index macro variable within the nested foreach loop: tobit theta ` index', ll(0) ul(1)

Brendan Over a year ago

Thanks; was only correcting the items previously identified, but you are of course correct.

Collectives™ on Stack Overflow

How to loop over excel sheet and lists in Stata?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related