Insert empty row by group conditionally

[Manqing Liu]

Hi,

I have a survey data like this:

input id sequence q1 q2 q3 q4 q5
001 1 1 3 2 1 5
001 2 3 2 1 5 6
001 3 4 5 2 9 0
001 4 2 4 2 5 9
002 2 1 4 2 1 0
002 3 4 2 1 0 9
003 4 2 1 9 0 9
004 1 1 3 2 9 8
004 3 2 3 1 9 2
004 4 2 0 9 4 3
end

Each participant filled the survey for multiple times, and the total number of times the survey was delivered is 4. Participant who did not reply at one of the four times will not have any record in the dataset. For example, 001 have all 4 inputs from 1-4, so didn't have any missing, but 002 missed 1, 4; 003 missed 1, 2, 3; 004 missed 2. How can I insert rows for those missing participants so that each id has 4 rows in the dataset like this:

input id sequence q1 q2 q3 q4 q5
001 1 1 3 2 1 5
001 2 3 2 1 5 6
001 3 4 5 2 9 0
001 4 2 4 2 5 9
002 1 . . . . .
002 2 1 4 2 1 0
002 3 4 2 1 0 9
002 4 . . . . .
003 1 . . . . .
003 2 . . . . .
003 3 . . . . .
003 4 2 1 9 0 9
004 1 1 3 2 9 8
004 2 . . . . .
004 3 2 3 1 9 2
004 4 2 0 9 4 3
end

[William Lisowski]

The fillin command is the tool you need.

. fillin id sequence

. list, noobs sepby(id)

  +--------------------------------------------------+
  | id   sequence   q1   q2   q3   q4   q5   _fillin |
  |--------------------------------------------------|
  |  1          1    1    3    2    1    5         0 |
  |  1          2    3    2    1    5    6         0 |
  |  1          3    4    5    2    9    0         0 |
  |  1          4    2    4    2    5    9         0 |
  |--------------------------------------------------|
  |  2          1    .    .    .    .    .         1 |
  |  2          2    1    4    2    1    0         0 |
  |  2          3    4    2    1    0    9         0 |
  |  2          4    .    .    .    .    .         1 |
  |--------------------------------------------------|
  |  3          1    .    .    .    .    .         1 |
  |  3          2    .    .    .    .    .         1 |
  |  3          3    .    .    .    .    .         1 |
  |  3          4    2    1    9    0    9         0 |
  |--------------------------------------------------|
  |  4          1    1    3    2    9    8         0 |
  |  4          2    .    .    .    .    .         1 |
  |  4          3    2    3    1    9    2         0 |
  |  4          4    2    0    9    4    3         0 |
  +--------------------------------------------------+