Will Stata automatically set the range of data when running loop?

[Giang Dao]

When I loop over many Excel sheets, I use "import" command to input the data. However, because each sheet has different number of rows, is it right that the option "cellrange" must be ajusted one by one? Can I just write 1 command and run the loop?

For example, I wrote the command like this:

foreach x of local x_list {
clear
import "path_including_`x'", sheet(S1) cellrange(A5:M27) firstrow
do ...
save "path_including_`x'", replace
}

The part "M27" is automatically set by Stata because 27 is the number of rows in the data sheet I import. I wonder when I run this loop, will Stata automatically set M30, M40, M100,... according to the range of rows in each data sheet, or will it import only till M27 as the first sheet?

Thanks a lot for your help.

[Baptiste Ottino]

Written like this, no, the cellrange will not be adjusted. However, you have different solutions you can try. First:

import excel "path_including_`x'", sheet(S1) firstrow

Could work if you have nothing in the cell range "A1:M4" (above your dataset). If you have stuff, you will have to drop the first few "observations" you get as a result.

[Giang Dao]

thanks for your help. i guess i must do as you said, and drop some observations, but then how can I set the first row as variables?

[daniel klein]

Nota that cellrange() requires only one argument and will figure out the second as appropriate; cellrange(A5) should do just fine. Note also that the loop as written in #1 runs over files not sheets.

See nrow (SSC) for renaming variables according to any row in the dataset. See xls2dta (SSC) as an alternative to the required loops.

Best
Daniel

[Giang Dao]

Thanks a lot Daniel!