importing Excel .xlsx file to Stata

[Jerome Anderson]

I have an Excel .xlsx file w/ 9337 observations. The 2nd variable in the file is labeled MEASURE. In Excel, it is defined as numeric with 2 decimal places. I import the file to Stata SE, latest version. The variable MEASURE is defined as double precision, %14.2f. I attempt to run anova Race MEASURE. I get the following error message: MEASURE: factor variables may not contain noninteger values. The lowest value in the column MEASURE is 0.03. There are 28 of these values. I attempted to recast the variable to integer. I get the error message: MEASURE: integer invalid. I tried using recast byte and recast float. Same error message each time: MEASURE: 9155 values would be changed; not changed. Several questions: 1. Why can't I use the recast command - why does it give me an error w/ integer, byte, float? And why the large number of values (222) not changed? I looked through all 9,337 observations and found nothing out of the ordinary. As noted, there are 28 values < 1, but not 222.I can run oneway Race MEASURE with no problems, but oneway does not keep the estimates for post hoc testing. I need the anova command.

Anyone else have a similar problem? Any suggestions?

[Clyde Schechter]

First, your variable Measure, whether recast or not, is unsuitable for use as a right-side variable in -anova- because it is a (quasi-)continuous variable. Any right-side variable in -anova- must be discrete, and discrete variables in Stata must take on non-negative integer values only.

-recast- refuses to convert Measure because it finds that doing so will cause a loss of data. If it were possible to -recast- a value like 0.33 to byte, it would presumably be changed to zero, which would be a meaningful change in its value. Moreover, values of 0.33 and 0.98 would both be collapsed to zero, reducing the amount of observed variation. Now, you can get -recast- to do this by using its -force- option, and then the conversion will be made, with the accompanying loss of data accuracy and variation. It is hard to think of a situation where this would be a sensible thing to do, however.

I think the real question here is not how to manipulate the Stata commands. The real question here is what is the variable Measure actually about and what kind of information it represents. Is it truly a discrete variable like, say, sex, or religion, or nationality would be, or is it actually a numeric variable? When I ask whether it is a numeric variable I mean: is it meaningful to apply numeric operations to it. For example, is it meaningful to perform operations like addition or subtraction on the values of measure? If observation A has a value x, and observation B has a value y, is there some real meaning to a value of x+y for observation C representing some sort of aggregation of the attribute measured in observations A and B?

In real discrete variables, these numerical properties are absent. Although we might code a sex variable with values 1 and 2, there is no meaning to 1+2, nor 2-1, in this context, nor is it sensible to say that either sex is "greater" than the other. It is these discrete variables that are the proper domain of applicability to -anova-.

If your variable Measure is actually discrete, then the values used to encode it were chosen in an unfortunate way. But the solution to this would be easy: just -egen measure_discretized = group(Measure)- and you will get a new variable with values 1, 2, 3,..., which you can then use in -anova-.

If your variable Measure is actually not discrete, then it is not possible to use it on the right side of -anova- and you need to pick some other analytic approach. Without knowing what Measure is actually about, nor anything about the research question(s), I can't advise what other approaches might be suitable.

[Nick Cox]

The title of the thread seems to have nothing to do with your problems, which arise within Stata.

1. Use of anova

With the syntax you used anova wants (requires) predictor variables to be factor variables, so coded by integers (only). MEASURE fails this test. As the help explains, there is code to override it, so that

anova Race c.MEASURE

gets around this problem. That said, my guess is that there is a simpler story here: you have the variables the wrong way round, and really want

anova MEASURE Race

That is, in say social, economic or medical contexts it seems more likely to me that race is a predictor, not an outcome or response.

2. Use ofrecast

On this story, there need to be no temptation to try forcing the variable into some other form. The purpose of recast is, and is only, to convert a numeric variable to another variable of different numeric type, so long as there is no loss of information. That is why your attempts to use it failed. Now recast does have a force option, but using it is usually a bad idea. If there is a need to round or otherwise simplify a variable, the best idea is to create a new variable from the old with syntax making the conversion explicit.

There may be other problems with your data.

EDIT I was writing this while Clyde Schechter was writing his. I hope our answers are complementary.

[Jerome Anderson]

Clyde, thank you for the informative response.

Yes, the values in MEASURE are continuous. They are the person measures generated by the Rasch model. I began w/ dichotomous variables (0,1), ran the original data (taken from the 2019 Parent and Family Involvement in Education Survey conducted by the National Center for Education Statistics) in Winsteps to generate item and person measures and now intend to analyze the measures with various categorical variables (Race, School_Type, School_Level). I don't know if you are familiar w/ Rasch measurement. This is not the place to enter into an extended discussion of what it is and what it does. Suffice it to say I have used this psychometric tool in other contexts and have had no problems analyzing the measures. t-tests and regression run just fine on the MEASURE variable.

I did run recast byte MEASURE, force to see what would happen. The resulting new variable truncated the 2 decimal places in MEASURE rather than rounding the original values.. However, it gave a message saying 9115 values were changed, not the 9337. Apparently there were values that had no decimal places and thus were not truncated. I then ran anova on the new truncated variable. The anova ran, but when i ran the pwcompare Race, mwcompare(tukey) pveffects commands, the error message i received was factor Race not found in list of covariates. This is the first time I have seen this error. I have run anova w/ pwcompare many times and never have seen this error.

I can live w/ truncated measures; the differences are not so great they would not be detrimental to my analyses. Why I can't generate the post hoc tests after a forced conversion of the original values baffles me. If I report anova results, i would also want to report post hoc tests and effect size. If you have thoughts on that issue, they would also be welcome.

[Jerome Anderson]

Thank you, Nick. My goof. I mixed up the DV and IV. I put the IV first instead of the DV. A simple mistake, but thank you both for the lessons. Everything worked fine once I fixed the basic problem. Sorry for the inconvenience.

[Nick Cox]

Thanks for the closure. I never reach for analysis of variance if I can reach for regression or a generalized linear model. What is a good idea for your project would depend on the distribution of MEASURE given Race. Is it close enough to normal to be really comfortable with anova?