Help replacing values for observations in a group with the value of an observation within a group that meets a specific criteria

[Rhina Torres]

Hello,
I need help replacing the variable "campus_grounds" with the "school_name" of the observation that contains "Yes" for in the "main_campus" variable. I want to do this for all observations that share the same psal_code. I tried

bysort psalcode: gen psal_grounds=school_name if main_campus=="Yes" , but this only applies the change to the observation that contains "yes". Below is a sample of my data set and the outcome I want to achieve:


clear
input int psalcode str50 school_name str3 main_campus float campus_grounds
3515 "Frank McCourt High School" "" .
3515 "Innovation Diploma Plus" "" .
3515 "The Urban Assembly School for Green Careers" "" .
3515 "The Global Learning Collaborative" "Yes" .
10526 "Marble Hill High School for International Studies" "" .
10526 "Bronx Engineering and Technology Academy" "" .
10526 "Bronx Theatre High School" "" .
10526 "New Visions Charter High School for the Humanities" "Yes" .
10526 "Bronx School of Law and Finance" "" .
10526 "English Language Learners and International Suppor" "" .
10526 "New Visions Charter High School for Advanced Math" "" .
end
[/CODE]


I would like to see:


clear
input int psalcode str50 school_name str3 main_campus float campus_grounds
3515 "Frank McCourt High School" "" "The Global Learning Collaborative"
3515 "Innovation Diploma Plus" "" "The Global Learning Collaborative"
3515 "The Urban Assembly School for Green Careers" "" "The Global Learning Collaborative"
3515 "The Global Learning Collaborative" "Yes" "The Global Learning Collaborative"
10526 "Marble Hill High School for International Studies" "" "New Visions Charter High School for the Humanities"
10526 "Bronx Engineering and Technology Academy" "" "New Visions Charter High School for the Humanities"
10526 "Bronx Theatre High School" "" "New Visions Charter High School for the Humanities"
10526 "New Visions Charter High School for the Humanities" "Yes" "New Visions Charter High School for the Humanities"
10526 "Bronx School of Law and Finance" "" "New Visions Charter High School for the Humanities"
10526 "English Language Learners and International Suppor" "" "New Visions Charter High School for the Humanities"
10526 "New Visions Charter High School for Advanced Math" "" "New Visions Charter High School for the Humanities"
end
[/CODE]


I appreciate your input in helping me figure this out. Thank you!

[Sergiy Radyakin]

clear
input int psalcode str50 school_name str3 main_campus str50 campus_grounds
3515 "Frank McCourt High School" "" .
3515 "Innovation Diploma Plus" "" .
3515 "The Urban Assembly School for Green Careers" "" .
3515 "The Global Learning Collaborative" "Yes" .
10526 "Marble Hill High School for International Studies" "" .
10526 "Bronx Engineering and Technology Academy" "" .
10526 "Bronx Theatre High School" "" .
10526 "New Visions Charter High School for the Humanities" "Yes" .
10526 "Bronx School of Law and Finance" "" .
10526 "English Language Learners and International Suppor" "" .
10526 "New Visions Charter High School for Advanced Math" "" .
end

list 

gsort psalcode -main_campus

list, sepby(psalcode)

by psalcode: replace campus=school_name[1]

list, sepby(psalcode)

Ultimately results in:

     +-------------------------------------------------------------------------------------------------------------------------------+
     | psalcode                                          school_name   main_c~s                                       campus_grounds |
     |-------------------------------------------------------------------------------------------------------------------------------|
  1. |     3515                    The Global Learning Collaborative        Yes                    The Global Learning Collaborative |
  2. |     3515                              Innovation Diploma Plus                               The Global Learning Collaborative |
  3. |     3515          The Urban Assembly School for Green Careers                               The Global Learning Collaborative |
  4. |     3515                            Frank McCourt High School                               The Global Learning Collaborative |
     |-------------------------------------------------------------------------------------------------------------------------------|
  5. |    10526   New Visions Charter High School for the Humanities        Yes   New Visions Charter High School for the Humanities |
  6. |    10526    Marble Hill High School for International Studies              New Visions Charter High School for the Humanities |
  7. |    10526    New Visions Charter High School for Advanced Math              New Visions Charter High School for the Humanities |
  8. |    10526                      Bronx School of Law and Finance              New Visions Charter High School for the Humanities |
  9. |    10526             Bronx Engineering and Technology Academy              New Visions Charter High School for the Humanities |
 10. |    10526                            Bronx Theatre High School              New Visions Charter High School for the Humanities |
 11. |    10526   English Language Learners and International Suppor              New Visions Charter High School for the Humanities |
     +-------------------------------------------------------------------------------------------------------------------------------+

[Sergiy Radyakin]

Please note:

- I had to change your float to str50 type for campus_grounds;
- it is not clear whether main_campus is ALWAYS present in each psalcode group;
- it is not clear whether main_campus has no duplicates in each psalcode group;
- whether you may need reproducibility in case the above uniqueness assumption is violated;
- you may wish to store the schools as labels for the numerical codes for efficiency, depending on what you are doing.

Best, Sergiy

[Rhina Torres]

Thank you Sergiy! To answer your questions:
main_campus is not always present for each psalcode.
main_campus contains no duplicates.

I do need to apply this for 200+ groups. In this case, should I create a dummy variable that indicates that the above code should apply to psalcodes with a main_campus? Thank you so much for all your help!

[Sergiy Radyakin]

main_campus is not always present for each psalcode.

In this case the main campus will be selected as the first school name. You can change the code to be:

 
 by psalcode: replace campus=school_name[1] if (main_campus[1]!="")

to leave it blank if there is no main campus in a certain group. No additional dummies are required.