Obs. nos. out of range r(198);

[Melanie Fan]

A quick question, what does it mean when stata warns in red color that :
Obs. nos. out of range
r(198);

I was working with a large dataset, with many worksheets within a excel, when I was trying to read in all worksheets, stata shows up this warning after a few minutes' running with actually no problem. I wonder what is the problem, could anyone help? Thanks a lot !!

best regards,
Melanie

[Nick Cox]

It seems that what you are trying to do clashes the number of observations in the dataset, or possibly with the number of observations allowed in a dataset.

This is a problem, which is why you got an error message.

 
help limits

will tell how big a dataset you can have.

It's difficult to say more. You don't give any exact details on which command(s) you were using. "trying to read in all worksheets"; does that mean import excel? Were you trying to read in worksheets with a loop? Guessing games can sometimes be fun, but please see FAQ Advice Section 12 for how to report problems more precisely.

[Melanie Fan]

It seems that what you are trying to do clashes the number of observations in the dataset, or possibly with the number of observations allowed in a dataset.

This is a problem, which is why you got an error message.

help limits

will tell how big a dataset you can have.

It's difficult to say more. You don't give any exact details on which command(s) you were using. "trying to read in all worksheets"; does that mean import excel? Were you trying to read in worksheets with a loop? Guessing games can sometimes be fun, but please see FAQ Advice Section 12 for how to report problems more precisely.

hi, Nick, thanks for the hint！ Sorry, that I did not put my code here, because it was with a loop, and I didn't know if this could be the problem since I have used this do file before for some other datasets successfully. but this time it run into a problem. I actually don't think the size of the dataset could be the problem, because this 123 dataset is no larger than the other datasets I used before. Anyway, I post my command here for ideas!

tempfile tem
save `tem', emptyok

import excel using "123.xlsx", describe

local n_sheets `r(N_worksheet)'
forvalues j = 1/`n_sheets' {
local sheet`j' `r(worksheet_`j')'
}

forvalues j = 1/`n_sheets' {
import excel using "123.xlsx", sheet(`"`sheet`j''"') clear
destring B C D E F G H I J K, replace force
gen code = `"`sheet`j''"'
append using `tem'
save `"`tem'"', replace
}


hope more suggestions could be coming. Thanks!

[Nick Cox]

OK; that confirms some guesses, but what does help limits tell you? How many observations are in each worksheet?

[Sergiy Radyakin]

set trace on
Tell us which command precedes the error message.
You can also see if a simpler version works or fails, without the gen code and destring statements.
Best, Sergiy

[Melanie Fan]

It varies, each worksheet could have 30 up to about 1000 observations. there are about 100 worksheets in each excel. In all, about 5000 obs in each excel file.

[Nick Cox]

I'd see where the problem is with something simpler.

 
import excel using "123.xlsx", describe

local n_sheets `r(N_worksheet)'
forvalues j = 1/`n_sheets' {
    local sheet`j' `r(worksheet_`j')'
}

forvalues j = 1/`n_sheets' {
      di "attempting `sheet`j''" 
    import excel using "123.xlsx", sheet(`"`sheet`j''"') clear
    destring B C D E F G H I J K, replace force
    save "`sheet`j''" 
}

[Clyde Schechter]

In all, about 5000 obs in each excel file.

What do you mean by "each excel file?" Is there something you aren't telling us? Everything else in your posts suggests that you have a single Excel file which contains about 100 worksheets. Is the code you are showing us itself embedded inside another loop over multiple Excel file or something like that?

[Melanie Fan]

What do you mean by "each excel file?" Is there something you aren't telling us? Everything else in your posts suggests that you have a single Excel file which contains about 100 worksheets. Is the code you are showing us itself embedded inside another loop over multiple Excel file or something like that?

Well, I have 10 excel files, they are very similar in structure. Each excel file has about 100 worksheets and each worksheet has 30 to 1000 obs. I only have to read in these 10 excel files separately for further studies. But the weird thing is that this command works fine for many of them. But out of the 10, 2 excel file cannot be read.

[Nick Cox]

Did you try #7? Did you try looking at the first worksheet that fails?

Even pessimistically 10 files * 100 worksheets * 1000 rows gives 1 million observations, which should be OK. So, the problem seems to be triggered somehow by the content of some worksheets.

[Clyde Schechter]

Well, if it's the last two that are being read in and you are appending them all into one big file then you may be encountering problems with memory limits, depending on the flavor of Stata you are running.

But if that's not the case, then I would follow Nick's advice above to diagnose where the problem is coming from. Then you can, if necessary, re-read the offending worksheet with -set trace on-, as suggested earlier by Sergiy, to get more information about the nature of the problem.

[Melanie Fan]

Hi, Nick, Thanks a lot for the suggestion, I have run it on my computer, but
a warning popped out too:
file SEACABLE_A.dta saved
attempting Tabelle1
no variables defined
r(111);

so I added something
drop in 1/5
gen code = `"`sheet`j''"'
inside the loop since it is also needed. But again the warning:

file SEACABLE_A.dta saved
attempting Tabelle1
Obs. nos. out of range



mmh, what to do now?

[Clyde Schechter]

Why did you -drop in 1/5-? What's going on in those observations that you wanted to eliminate? In any case, there appears to be something wrong with worksheet Tabelle1. I would first visually inspect that worksheet in Excel: the problem might be obvious. (Based on what you got before you made your additions to the code, perhaps Tabelle1 has nothing in it.) If it isn't obvious, I would also run the following:

set trace on
import excel using 123.xlsx, sheet(`"Tabelle1"') clear

That way you will find out just where -import excel- is choking on this data set.

[Nick Cox]

A wild guess is just that the worksheet is empty.

[Sergiy Radyakin]

Melanie, there was a bug in Stata 12, which could he helped if you open an Excel file and save it with Excel again. The important part is true Excel, not another software that produces xlsx files.
The symptoms are somewhat different from what I experienced, but not completely far fetched.
set trace on and import the file again. Cite the error lines. If files are not secret, sharing the file can save time.
Sergiy