st: Replacing valid values with the 2nd highest ranking person

["Carter Rees" <carterrees@gmail.com>]

Statalist,

Using Stata 10 Windows XP

I am working on a valid value replacement issue involving friends who are
ranked according to importance to a respondent.  Austin Nichols kindly
provided an answer to my initial question
(http://www.stata.com/statalist/archive/2007-06/msg00142.html).

The final version of the code Austin provided me off list is:

clear
input id mf1 mf2 mf3 mf4 mf5 ff1 ff2 ff3 ff4 ff5
1 . . 3 1 4 . . . . .
2 2 4 3 4 5 5 2 1 1 2
3 3 . . . . 4 . . . .
4 . 1 1 3 3 1 4 3 3 1
5 . . . . 4 . . . . 2
6 5 2 4 . . 1 . . . .
7 . . . . . . 2 . . .
8 1 . . . . . . . . .
9 . . . . . . . 3 . .
10 . . . . . 1 . . . .
11 . 2 3 4 5 8 9 3 4 3
12 . . 3 4 5 8 2 1 4 5
13 . . 3 5 4 . 1 2 3 4
14 . . . 8 5 1 4 6 7 3
15 . . . . . 2 99 4 . .
16 3 4 6 7 7 . . . . .
17 . . . . . 2 . 4 5 5 
18 . 2 . 4 . 1 2 . 4 .
19 . . . 3 4 1 2 . . .
20 3 4 5 2 1 . 2 4 . 5
21 . 3 4 5 6 . . 2 . 5
22 . . 0 . . 2 1 0 0 .
23 . . 3 . 3 0 0 . 0 .
24 . . 0 0 0 1 0 . 0 0
25 . 0 0 0 1 0 0 2 . .
26 2 1 0 0 . . . 0 . .
27 . 1 . . 1 . . . . 0
28 . . 2 1 . . . . 1 2
29 . 1 4 . 1 . . . 0 0
30 1 2 3 4 . . . . 4 .
31 . . 3 5 . . . . 2 .
32 . . 4 . . . . . 6 7
33 . . 2 6 . 8 . . . .
34 . . 9 . . . . . 7 .
end
reshape long mf ff, i(id) j(rank)
gen mok=rank if mf<.
bys id (mok): g topfriend=mf[1]
by id: g fsex="m" if mf[1]<.
by id: g used=rank[1] if mf[1]<.
gen fok=rank if ff<.
bys id (fok): replace topfriend=ff[1] if rank[1]<used & ff[1]<.
by id: replace fsex="f" if rank[1]<used & ff[1]<.
by id: replace used=rank[1] if rank[1]<used & ff[1]<.
by id: replace fsex="mf" if rank[1]==used & ff[1]<. & fsex!="f"
by id: replace topfriend=(ff[1]+topfriend)/2 if rank[1]==used & ff[1]<. &
fsex!="f"
drop mok fok
reshape wide mf ff, i(id) j(rank)
aorder
li fsex used topfriend mf* ff* id, noo clean

ID is a unique identifier for each respondent mf* is the male friend
variable with that male friend's rank denoted.  MF1 is the first ranked male
friend with values equal to MF1's response to a question on the survey.  FF*
follows the same format except it is for female friends.  Male and female
friends were ranked separately and therefore a respondent could have a male
and female friend with the same rank.    The code also created a USED
variable indicating the rank of the friend(s) used and an FSEX variable
indicating the sex of the friend(s) used.

What this code allowed me to do is create a variable called TOPFRIEND which
took on the value of the highest ranked friend with a valid value named by
the respondent.  If the respondent named both a male and female friend who
had the same rank, the average of that male/female pair was assigned to
TOPFRIEND.  

I would now like to take this code and create three new variables which
represent 3 different friend replacement schemes.  

First I would like to create a variable called TOPFRIEND2 which takes on the
value of the highest ranked friend after the friend designated in TOPFRIEND.
For example, in ID 1 TOPFRIEND would be male friend 3 (TOPFRIEND = 3) and
TOPFRIEND2 would be male friend 4 (TOPFRIEND = 1).  In ID 2 TOPFRIEND would
be male/female 1 average (TOPFRIEND = 3.5) and TOPFRIEND2 would be
male/female 2 average (TOPFRIEND2 = 3).  In ID 27 TOPFRIEND would be male
friend 2 and TOPFRIEND2 would be male/female 5 average.  

Next, I would like to create a variable called TOPRELFRIEND.  This variable
would be set equal to the highest ranked friend with a valid value within
each gender and average between the two genders if there is at least one
valid value for each.  For example, in ID 22 male friend 3 is the highest
ranked male friend and female friend 1 is the highest ranked female friend.
Thus, TOPRELFRIEND would be equal to (0+2)/2 = 1.  In ID 34 TOPRELFRIEND
would be equal to the average of male friend3 and female friend 4: (9+7)/2 =
8.  Lastly, following the same logic as TOPRELFRIEND I would like to create
TOPRELFRIEND2 which serves the same function as TOPFRIEND2 but follows the
logic of TOPRELFRIEND.

My own attempt at this is getting convoluted and ugly.

I appreciate the assistance.

Carter



*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/