# how to treat NA values in Compustat and CRSP

In some years, some accounting values of some companies are missing in Compustat and CRSP. How do I treat those missing values ? Should I replace those with zeros or just simply delete years in which missing values are represented ?

For example:

gvkey   datadate    fyear   current aseet     Total Asset       Current Liability
1010    19951231    1995                        2015.8
1010    19961231    1996                        2218.6
1010    19971231    1997                        3181.3


You're going to have to use judgement. There are situations where treating missing values as 0 would be insane. In general, I'd be immensely cautious.

On the other hand, there are situations where it's reasonable to attempt an alternative calculation and situations where using 0 may be reasonable.

A nice example can be found in Kenneth French's description of how Fama and French calculate book equity:

Book Equity ... is the book value of stockholders’ equity, plus balance sheet deferred taxes and investment tax credit (if available), minus the book value of preferred stock. Depending on availability, we use the redemption, liquidation, or par value (in that order) to estimate the book value of preferred stock. Stockholders’ equity is the value reported by Moody’s or Compustat, if it is available. If not, we measure stockholders’ equity as the book value of common equity plus the par value of preferred stock, or the book value of assets minus total liabilities (in that order). See Davis, Fama, and French, 2000, “Characteristics, Covariances, and Average Returns: 1929-1997,” Journal of Finance, for more details.

Following Fama and French, you can take a first crack at calculating book equity using the SQL code:

SEQ - COALESCE(PSTKRV, PSTKL, PSTK, 0) + COALESCE(TXDITC,0) as be

The COALESCE function returns the first non null result in the list. For the book value of preferred stock, try PSTKRV first, then PSTKL, then PSTK. Treat the tax deferred assets plus investment tax credit (TXDITC) as zero if it's unavailable.

The code here written by Palacios from CRSP and Vora from Penn State does what I described above.

• Good point! Upvoted. – phdstudent Jun 4 '18 at 21:43
• @phdstudent I agree with you though that treating missing values as zeros can introduce serious biases. In an ideal world, you'd never do it! On the other hand, we don't live in an ideal world. There's a huge literature across economics, medicine, etc... on dealing with missing data and what approach might be 2nd best in various contexts. – Matthew Gunn Jun 4 '18 at 22:03