Moving Up nonmissing values and keep "state" variables

[Nang Widaryoko]

Hello everyone. I am so sorry to ask again.....

I have a set of data. Some of them have missing values. I want to move up the non-missing values while keeping the "state" 1-10 (without delete it). So I just use nonmissing to fill in the missing data without deleting the state. The dataset example is:

id	state	age	year	status
125	1	.	.	.
125	2	15	1970	0
125	3	20	1975	1
125	4	.	.	.
125	5	23	1978	1
125	6	.	.	.
125	7	.	.	.
125	8	28	1992	2
125	9	.	.	.
125	10	.	.	.

I'd like to change it to

id	state	age	year	status
125	1	15	1970	0
125	2	20	1975	1
125	3	23	1978	1
125	4	28	1992	2
125	5	.	.	.
125	6	.	.	.
125	7	.	.	.
125	8	.	.	.
125	9	.	.	.
125	10	.	.	.

Could someone please help how to write the command?

thankyou in advance

bestregards
nang

[Clyde Schechter]

I believe the following will work. It does so in your example data, and also in an extended example that includes another id with a different number of observations having missing values.

* Example generated by -dataex-. For more info, type help dataex
clear
input int id byte(state age) int year byte status
125  1  .    . .
125  2 15 1970 0
125  3 20 1975 1
125  4  .    . .
125  5 23 1978 1
125  6  .    . .
125  7  .    . .
125  8 28 1992 2
125  9  .    . .
125 10  .    . .
137  1  .    . .
137  2 15 1970 0
137  3 20 1975 1
137  4  .    . .
137  5 23 1978 1
137  6  .    . .
137  7  .    . .
137  8 28 1992 2
137  9 29 1993 0
137 10  .    . .

end

assert missing(age) == missing(year) & missing(year) == missing(status)
drop if missing(age)
by id (state), sort: replace state = _n

sort state
if state[_N] < 10 {
    local expander = 10 - state[_N] + 1
    expand `expander' in L
    foreach v of varlist age year status {
        replace `v' = . in -`=`expander'-1'/L
    }
    gen byte to_end = missing(age)
    by id (state to_end), sort: replace state = _n
}
fillin id state

The easy part of this is getting rid of the observations with missing values and renumbering them consecutively from one. The harder part is then extending the data set so that every id has exactly 10 observations. (Actually if there are any id's in the data set that have more than 10 observations without missing data, this code will give every id that same number of observations.)

In the future, when showing data examples, please use the -dataex- command to do so, as I have done here. If you are running version 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.

[Carlo Lazzaro]

Nang:
what follows is possibly too basic given Clyde's previous reply, but just in case...:

. sort year

. bysort id: replace state=_n


. list

     +-----------------------------------+
     |  id   state   age   year   status |
     |-----------------------------------|
  1. | 125       1    15   1970        0 |
  2. | 125       2    20   1975        1 |
  3. | 125       3    23   1978        1 |
  4. | 125       4    28   1992        2 |
  5. | 125       5     .      .        . |
     |-----------------------------------|
  6. | 125       6     .      .        . |
  7. | 125       7     .      .        . |
  8. | 125       8     .      .        . |
  9. | 125       9     .      .        . |
 10. | 125      10     .      .        . |
     +-----------------------------------+

[Clyde Schechter]

Good grief! Carlo's solution in #3 is so much better than mine!

[Carlo Lazzaro]

Thanks, Clyde!
It's just an occasional fluke!

[Nang Widaryoko]

It's so great...both are work...it's very helpfull......thank you verymuch Clyde Schechter and Carlo Lazzaro