Collapse produces different but optically similar results

[Leon Schmidt]

Dear all,

I am having a weird problem with the collapse command. I collapse my data to a sum and there are no missing values in the data. After every collapse I compare the result to the previous collapse. Stata tells me that the two datasets are not completely identical. However, if I look at the respective observations in the browser the do seem to be completely identical (the numbers after the period are also identical). Does anyone has experienced this behavior and has a solution?

Thank you very much!

All the best
Leon

[Leon Schmidt]

I guess that it has something to do with the way Stata stores the data. By default it creates the collapsed variables in the format double %10.0g . That is also the original format before the collapse. So while the variables look alike after the collapse, my guess is that internally in Stata the variables are still not completely identical. However, I have not found a solution yet.

[Leon Schmidt]

I ended up rounding all variables, then they were equal. However, I believe there must be a better option. If someone knows something I´d appreciate your feedback. Thank you very much and all the best!

[Nick Cox]

I have not understood the problem yet.

The same command on the same data should give the same results. You haven't given a reproducible example with data and code that contradicts that.

Other way round, if two sets of results look different, the answer should be that the code or the data were subtly different in some way (or clearly different if you look carefully enough).

So, examples please.

[Leon Schmidt]

Dear Nick,

Thank you very much for your reply!

I am using firm-level data so this seems too big to provide through dataex.

I did some experimenting and the problem arises only if I include many observations (like 2000 and above). For smaller datasets it does not appear.

This is what I did: I have the sales value for each firm, I know their industry, and the year. I want to collapse this data to yearly industry-totals. There are no missing values. The year is an int value, the industry a byte, and the sales variable a double value. I run the following code

use before_collapse, clear
collapse (sum) i_sale, by(industry year)
save test, replace

use before_collapse, clear 
collapse (sum) i_sale, by(industry year)
cf * using test

However, it tells me that there are mismatches in the sales variable. If I inspect both datasets in the browser they appear identical. However, I suspect that within Stata the precision is somehow different. For now I rounded the collapsed variable but I think this is not the best solution.

Thank you very much for your help!

All the best
Leon

[Nick Cox]

Perhaps you can share your file with StataCorp technical support. I agree that on the face of it this shouldn't happen.

[Joseph Coveney]

Maybe you could try

set type double

before you begin using collapse, which uses float by default.

[Leon Schmidt]

Thank you both for your help Nick and Joseph! This is what the Stata technical support said too. Apparently it has something to do with a lack of precision for float variables. However, using the recast command to change the variables into double or creating the variables themselves as doubles did not resolve the error.

In the end I ended up using code like this:

use before_collapse, clear
bysort industry year: egen total_sale = total(i_sale)
drop i_sale
duplicates drop
save test 

use before_collapse, clear
bysort industry year: egen total_sale = total(i_sale)
drop i_sale
duplicates drop
cf * using test

This does not produce any error when running it. The resulting dataset also looks identical to the one produced by the collapse-command. However, there are small differences visible if one looks at the decimal places far out. But it is consistent.

Thank you again and if anyone has other advice I´d appreciate this too!

[Nick Cox]

On further thought, it might be guessed that differences in sort order could lead to some small differences in which bits were dropped in working out a cumulative sum.

[Leon Schmidt]

I thought so too. I don´t sort the data before collapsing, but maybe Stata does that when running the command. The technical support also forwarded the issue, maybe it will be addressed in the future.Thank you very much for your help!

[Joseph Coveney]

I don´t sort the data before collapsing, but maybe Stata does that when running the command.

Yes, it does. It sorts the observations within each industry and year combination, and it uses a random sorting order to do so. That pseudorandom-number seed got incremented during the sorting performed during execution of the first collapse command and so is different for the second's execution.

Try this:

set type double // Recommended for -collapse-, especially

use before_collapse, clear

query sortseed
local original_seed = r(sortseed)
collapse (sum) i_sale, by(industry year)

save test, replace


use before_collapse, clear

set sortseed `original_seed'
collapse (sum) i_sale, by(industry year)

cf * using test

help sortseed

[Leon Schmidt]

Thank you very much Joseph! Indeed, this code worked!

I don´t know if I should like this behavior of Stata though. It´s not good for replication purposes since the results in the decimal places depend on the sortseed. But then again those probably don´t matter much and it´s probably computationally required.

Thank you all very much for your input and solving this puzzle!

[Maarten Buis]

I don't think that sorting ties randomly was done out of convenience. Conceptually, this is how it should be done: if observations are ties then you don't know who should come first. It is also considered a feature. If your results depend on something as random as the sort order in your data, then it is good to inform you about that.

[Leon Schmidt]

Yes, the more I think about it, it seems to make sense.

I also discovered that the sortseed is the same when opening Stata. It changes after running a sort command but always changes in the same way. So the following code actually works too:

use before_collapse, clear
collapse (sum) i_sale, by(industry year)
save test, replace  

QUIT STATA AND REOPEN IT  

use before_collapse, clear
collapse (sum) i_sale, by(industry year)
cf * using test

The error just emerged when I ran it in one session.

[Sergio Goldbaum]

just to mention that I have a similar problem. I run the same collapse command

collapse (sum) n_recl, by(tratados date4)

with the same database five or six times, each time resulting a different new dataset.