Fill-in missing data based on existing data

[Michael Chen]

Dear all, I'm posting here in desperate search for help.
I have an example data set in long format with 5 participants (id=101-105).
This is a four-year study that collected data from each participant annually (2010-2013).
However, race ("race") data are incomplete.
I'd like to create a new variable ("race2") that extracts all available race data and apply them across all the years.
So far, my attempt involved creating 2 loops (i=loop thru id; y=loop thru year) but no success.
Here's a screenshot to illustrate what I mean.
(Note: actual data has has 2+ million rows, so wouldn't be able to do it manually.)
Thanks,
Michael

[Clyde Schechter]

No need for any loops. This is a one-liner:

by id (race), sort: replace race = race[1]

Added: Actually the above solution is a bit over-simplified. It is possible that for some id's there will be inconsistent values of race recorded in different years. How would you want to handle that situation? Which value would you want to use to replace the missing values? Now, maybe you have an extremely well curated data set that doesn't have this problem--in which case the solution proposed is adequate. Still, with a 2,000,000 observation data set, you can't know that just by visually scanning the data. So really, before running the above, it is best to verify that no id has more than one non-missing value of race reported. You can do that with:

by id (race), sort: assert race == race[1] | missing(race)

If your data set has no inconsistent values for any person, this command will produce no output and Stata will proceed to the next command. If you do have some inconsistent values, then this command will give you an error message. In that case, you need to decide how you want to handle these exceptions, and if you post back, I will try to give you code that will implement your preferred solution.

Note: actual data has has 2+ million rows, so wouldn't be able to do it manually.

Even if the actual data set had only two observations ("rows") you should never do anything with your data manually. You should always use commands, and you should always keep a complete log of everything you do so you have an audit trail. Without an audit trail, nobody can ever know how your results were arrived at, so they have no reason to take them seriously. And without an audit trail, if you had to come back to this in, say six months, you would no longer remember what you did either.

In the future, please do not show example data with screenshots. There are several reasons: 1. screenshots are often unreadable on other people's setups. 2. If the problem is sufficiently complicated to need to try out code, there is no way to import a screenshot into Stata to work with it. 3. Screenshots often fail to show the necessary metadata to properly solve the problem. In this particular case, none of these problems arose. But that's just luck. In the future, use the best way to show example data: the -dataex- command. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Nick Cox]

See also https://journals.sagepub.com/doi/pdf...6867X231196519 for more on Clyde Schechter 's first point.

[Michael Chen]

Hi all, thanks for all the helpful tips and resource referral so far, really appreciate the pointers!

To Clyde Schechter's initial follow-up (which is hitting this right in the head):

It is possible that for some id's there will be inconsistent values of race recorded in different years. How would you want to handle that situation? Which value would you want to use to replace the missing values?

I'd like for the new variable being generated (race2) to be equivalent to the "first chronological appearance" of race data (race). At the moment struggling to figure out how to make it happen.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(pid cid year race race2)
548 180 2005 . 1
548 184 2005 . 1
548 184 2006 1 1
548 184 2007 1 1
548 184 2007 1 1
548 188 2007 1 1
548 188 2007 1 1
548 188 2010 1 1
end
label values race racel
label def racel 1 "1.white", modify

Thank you again for your time and help!

[Nick Cox]

There is an egen function first() on egenmore at SSC,

. ssc type _gfirst.ado
*! 1.0.0 NJC 31 May 2000 
program define _gfirst 
        version 6.0
        gettoken type 0 : 0
        gettoken g 0 : 0
        gettoken eqs 0 : 0
        syntax varname [if] [in] [, BY(varlist) ] 
        marksample touse, strok
        tempvar order 
        gen long `order' = _n 
        sort `touse' `by' `order' 
        * ignore user-supplied `type' 
        local type : type `varlist' 
        qui by `touse' `by' : gen `type' `g' = `varlist'[1] if `touse'
end

but you can do this from first principles.

Think of this as a problem that hinges on sorting. For each person,

sort non-missing values before missing
within non-missing values, sort on year
pick the first value
sort back again

This will do it. I added some more cases as a slightly stronger check on ideas.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(pid cid year race race2)
548 180 2005 . 1
548 184 2005 . 1
548 184 2006 1 1
548 184 2007 1 1
548 184 2007 1 1
548 188 2007 1 1
548 188 2007 1 1
548 188 2010 1 1
42 . 2020 . 1
42 . 2021 1 1
43 . 2022 . . 
end
label values race racel
label def racel 1 "1.white", modify

gen wanted = missing(race)
bysort pid (wanted year) : replace wanted = race[1]

sort pid year 
list, sepby(pid)
     +---------------------------------------------+
     | pid   cid   year      race   race2   wanted |
     |---------------------------------------------|
  1. |  42     .   2020         .       1        1 |
  2. |  42     .   2021   1.white       1        1 |
     |---------------------------------------------|
  3. |  43     .   2022         .       .        . |
     |---------------------------------------------|
  4. | 548   184   2005         .       1        1 |
  5. | 548   180   2005         .       1        1 |
  6. | 548   184   2006   1.white       1        1 |
  7. | 548   184   2007   1.white       1        1 |
  8. | 548   184   2007   1.white       1        1 |
  9. | 548   188   2007   1.white       1        1 |
 10. | 548   188   2007   1.white       1        1 |
 11. | 548   188   2010   1.white       1        1 |
     +---------------------------------------------+

It's still prudent to check for inconsistent values. For that egen functions min() and max() could be useful.

[Michael Chen]

Hi all, thank you for the valuable guidance so far - really much appreciated!

As a follow up to the sorting line:

 bysort pid (wanted year) : replace wanted = race[1]

How do I account for cases where the "first item" (ie, "race[1]") in the sorted list contains an "invalid" response?

For example, Dave's validated race is Asian - however, this info was not properly captured during his first 5 utilizations (ie, in 2000, 2001, 2003, 2005, 2005).

Thank you!

* Example generated by -dataex-. For more info, type help dataex
clear
input int pid str5 name int year byte(race racev1 racev2)
500 "Bob"   2000  1  1 1
500 "Bob"   2002  1  1 1
500 "Bob"   2002 -8  1 1
500 "Bob"   2010 -8  1 1
503 "Chuck" 2001  2  2 2
503 "Chuck" 2010  .  2 2
510 "Dave"  2000 -8 -8 3
510 "Dave"  2001 -8 -8 3
510 "Dave"  2003 -8 -8 3
510 "Dave"  2005 -8 -8 3
510 "Dave"  2005 -8 -8 3
510 "Dave"  2007  3 -8 3
510 "Dave"  2010  3 -8 3
end
label values race racel
label values racev1 racel
label values racev2 racel
label def racel -8 "-8.dk/ref", modify
label def racel 1 "1.white", modify
label def racel 2 "2.black", modify
label def racel 3 "3.asian", modify

[Clyde Schechter]

It is a bad practice in Stata to use numerical codes to denote missing or indeterminate values. I know this is a common practice in other statistical package, but it works poorly in Stata and leads to problems like yours, and several others. So what I would do is

recode race1 (-8 = .d)
label values race1 .d "dk", modify

.d is one of Stata's "extended" missing values. (There is one for each lower case letter of the alphabet.) Like the system missing value (.), it sorts at the end, not the beginning, so now the code provided earlier will work properly. It is also important to do this before you attempt any calculations with these variables. For example, if you were trying to do a regression analysis with race as a categorical predictor variable, you would be unable to use factor variable notation (which only works with positive integers, not negative ones, but tolerates missing values). The only thing you lose by doing things this way, is that if you just -tab race1-, the dk value will no longer show up. But you can work around that by coding it as -tab race1, miss-, which will list a row for missing values in the output table as well.