Replace with value from another observation?

[Aske Halling]

Hi Statalist!

I am so please to be on board this great list that I have often used with great please. Now I have a question of my own:

I have data that looks like this:

ID	ID_of_Leader	Employee_score	Mean_employees
1	.	.	.
2	1	0.2	0.4
3	1	0.4	0.4
4	1	0.6	0.4
5	6	0.5	0.5
6	.	.	.
…

I have calculated the mean of the employees using egen with bysort, but now I want to create a new variable that capures the mean of the employees on the relevant leader and I can’t figure out how to do this. Can anyone help me?

Best regards
Aske

[Carlo Lazzaro]

Aske:
I'm not perfectly clear with what you're after.
That said, something along the following lines can get you near your goal:

 bysort ID_of_Leader: egen flag=mean( Mean_employees )

or

 bysort ID_of_Leader: egen flag=mean( Employee_score)

[Nick Cox]

Consider this example using rangestat (SSC)

clear 
input ID    ID_of_Leader    Employee_score    Mean_employees
1    .    .    .
2    1    0.2    0.4
3    1    0.4    0.4
4    1    0.6    0.4
5    6    0.5    0.5
6    .    .    .
end 

rangestat wanted=Employee_score, int(ID_of_Leader 0 0) 

list, sepby(ID_of_Leader) 

     +-------------------------------------------------+
     | ID   ID_of_~r   Employ~e   Mean_e~s      wanted |
     |-------------------------------------------------|
  1. |  1          .          .          .           . |
     |-------------------------------------------------|
  2. |  2          1         .2         .4   .40000001 |
  3. |  3          1         .4         .4   .40000001 |
  4. |  4          1         .6         .4   .40000001 |
     |-------------------------------------------------|
  5. |  5          6         .5         .5          .5 |
     |-------------------------------------------------|
  6. |  6          .          .          .           . |
     +-------------------------------------------------+

rangestat generates doubles automatically. The example just exposes (again!) that many multiples of 0.1 can't be held exactly because they don't have simple binary approximations.

[Aske Halling]

Thank you for the help so far! Unfortunately, I wasn't very good at explaining my self as Carlo points out, so I will try once again.

What I have is basically hierarchical data, where employees are nested within leaders. What I want is what is called 'Wanted' in the table below. That is, I want to 'copy' the mean of the employees to their relevant leader and have missing values for all employees.

ID	ID_of_Leader	Employee_score	Mean_employees	Wanted
1	.	.	.	0.4
2	1	0.2	0.4	.
3	1	0.4	0.4	.
4	1	0.6	0.4	.
5	6	0.5	0.5	.
6	.	.	.	0.5

I hope I made myself understandable this time!

[Carlo Lazzaro]

Aske:
what about:

. bysort ID_of_Leader: g Wanted_2= Mean_employees if _n==1
(4 missing values generated)

. list

     +---------------------------------------------------------+
     | ID   ID_of_~r   Employ~e   Mean_e~s   Wanted   Wanted_2 |
     |---------------------------------------------------------|
  1. |  4          1         .6         .4        .         .4 |
  2. |  3          1         .4         .4        .          . |
  3. |  2          1         .2         .4        .          . |
  4. |  5          6         .5         .5        .         .5 |
  5. |  6          .          .          .       .5          . |
     |---------------------------------------------------------|
  6. |  1          .          .          .       .4          . |
     +---------------------------------------------------------+

*Assuming that -Wanted_2- will replace -Wanted-*

[Aske Halling]

Hi Carlo

Thanks for the suggesting, but I am still not quite there. The basic problem is that the respondents that should have the values on 'Wanted' (in the example ID 1 and 6) does not contain the relevant value in any variable. It is his employees that has the relevant value - hence, I want to 'move' the mean of the employees to the leader with the relevant ID.

In your example, it is not ID 1 and 6 how gets the value on "Wanted_2" but ID 4 and 5 (who in my example are employees, not leaders).

Sorry for my lack of ability to make my self clear.