Create variables with 1st, 2nd, and 3rd non-missing values in row

[Noah Spencer]

Hello,
I have a dataset that looks like this:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(mom_id m_79_last_school m_80_last_school m_81_last_school m_82_last_school m_83_last_school m_84_last_school m_85_last_school)
1 6726    . .    .    . .    .
2 5630    . .    .    . .    .
3 6575 7244 .    .    8156 .    .
4    .    . .    .    . .    .
5    .    . .    .    . .    .
6    .    . .    . 8156 . 8979
7    .    . .    . 8187 .    .
8 5965    . .    .    . .    .
9    .    . . 7791    . .    .
end
format %td m_79_last_school
format %td m_80_last_school
format %td m_81_last_school
format %td m_82_last_school
format %td m_83_last_school
format %td m_84_last_school
format %td m_85_last_school

I want to generate three new variables that contain the first, second, and third (from left to right) non-missing values in a row, such that my resulting dataset looks like this:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(mom_id m_79_last_school m_80_last_school m_81_last_school m_82_last_school m_83_last_school m_84_last_school m_85_last_school first second third)
1 6726    . .    .    . .    . 6726    .    .
2 5630    . .    .    . .    . 5630    .    .
3 6575 7244 .    . 8156 .    . 6575 7244 8156
4    .    . .    .    . .    .    .    .    .
5    .    . .    .    . .    .    .    .    .
6    .    . .    . 8156 . 8979 8156 8979    .
7    .    . .    . 8156 .    . 8156    .    .
8 5965    . .    .    . .    . 5965    .    .
9    .    . . 7791    . .    . 7791    .    .
end
format %td m_79_last_school
format %td m_80_last_school
format %td m_81_last_school
format %td m_82_last_school
format %td m_83_last_school
format %td m_84_last_school
format %td m_85_last_school
format %td first
format %td second
format %td third

If anyone knows how I can do this, it would be much appreciated!

[Clyde Schechter]

reshape long m_@_last_school, i(mom_id)
drop if missing(m__last_school)
by mom_id (_j), sort: gen first = m__last_school[1]
by mom_id (_j): gen second = m__last_school[2]
by mom_id (_j): gen third = m__last_school[3]
reshape wide

[Noah Spencer]

Thanks very much Clyde Schechter!

[Nick Cox]

Here's one way to do it:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(mom_id m_79_last_school m_80_last_school m_81_last_school m_82_last_school m_83_last_school m_84_last_school m_85_last_school)
1 6726    . .    .    . .    .
2 5630    . .    .    . .    .
3 6575 7244 .    .    8156 .    .
4    .    . .    .    . .    .
5    .    . .    .    . .    .
6    .    . .    . 8156 . 8979
7    .    . .    . 8187 .    .
8 5965    . .    .    . .    .
9    .    . . 7791    . .    .
end
format %td m_79_last_school
format %td m_80_last_school
format %td m_81_last_school
format %td m_82_last_school
format %td m_83_last_school
format %td m_84_last_school
format %td m_85_last_school

egen all = concat(m_*_last_school), p(" ")
replace all = subinstr(all, ".", "", .)
forval j = 1/3 { 
    gen wanted`j' = real(word(all, `j'))
    format wanted`j' %td 
}

l mom_id wanted? 

    +--------------------------------------------+
     | mom_id     wanted1     wanted2     wanted3 |
     |--------------------------------------------|
  1. |      1   01jun1978           .           . |
  2. |      2   01jun1975           .           . |
  3. |      3   01jan1978   01nov1979   01may1982 |
  4. |      4           .           .           . |
  5. |      5           .           .           . |
     |--------------------------------------------|
  6. |      6   01may1982   01aug1984           . |
  7. |      7   01jun1982           .           . |
  8. |      8   01may1976           .           . |
  9. |      9   01may1981           .           . |
     +--------------------------------------------+

And here is another. You need rowsort from the Stata Journal

rowsort m_??_last_school, gen(wanted1-wanted7)

format wanted? %td 

l mom_id wanted1-wanted3