st: RE: RE: Question about data management - how to combine 2 rows of data

[Nick Cox <n.j.cox@durham.ac.uk>]

A crucial detail here: 

The -by:- prefix should be 

bysort id (day pill_morning): 

as otherwise only blocks of single observations are identified! However, you need only -sort- once. 

The best combination looks in general like 

max(varname[1], varname[2]) 

as that ignores missings. So I would do this 

sort id day

foreach v in pill_morning pill_afternoon pain_morning pain_afternoon { 
	by id: replace `v' = max(`v'[1], `v'[2]) if _n == 1 
} 

by id: drop if _n == 2 

I assume gender remains constant. 

Nick 
n.j.cox@durham.ac.uk 

Jean-Francois.Bertrand@fin.gc.ca

The general approach I would use (might not be the most efficient way) would be:

I assume "missing" is a "." so that once sorted it will be the last observation for day 1.

bysort id day pill_morning: replace pill_afternoon = pill_afternoon [_n+1] if day == 1 
...
bysort id day pill_morning: drop if _n == 2

Maria Cecilia Vieira da Silva

I have a problem with my panel data. Day 1 is duplicate for all the ids. The first "day 1" observation refers to the morning variables and the second 'day 1" observation refers to the afternoon variables. The rest of the days are fine - they are listed just once in the panel.
I would like to combine the rows for "day 1" and keep only 1 observation, so that I have information on the morning and afternoon variables combined in one row and would like to keep the variables that are invariable to "day".
Any suggestions?
I thought about using collapse, but it would change the names of the variables that are valid for the rest of the data set.  

id            day         pill_morning       pill_afternoon   pain_morning    pain_afternoon        sex
1              1              1                              missing                 5                              missing     M
1              1              missing                 3                              missing                 9                   M
1              2              1                              3                              1                              2            M
1              3              3                              5                              0                              1               M
2              1              2                              missing                 8                              missing     F
2              1              missing                 1                              missing                     3                F
2              2              0                              1                              0                              3              F
2              3              1                              0                              2                              0            F

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/