Repeat values from a variable in a new variables by group

[Federica Becca]

Hi everyone! I have this dataset for Colombia, with country code, department code, year, a key variable with age groups and number of births per age group, department and year. I need to create a new variable only with missing information (values with Sin information "170005_1998_Sin information") repeated for the same department and year. Ex. the first department is 005 = Antioquia for the year=1998 and missing values for the age group are = 774 (for 774 births there is no information about the mother's age). I need a new variable with 774 repeated across the 9 age groups for Antioquia in 1998, for further investigation and merges.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float raw_country str3 raw_geolev1 double raw_yearbir str28 key float births_count
170 "005" 1998 "170005_1998_10-14" 756
170 "005" 1998 "170005_1998_15-19" 21667
170 "005" 1998 "170005_1998_20-24" 26981
170 "005" 1998 "170005_1998_25-29" 21990
170 "005" 1998 "170005_1998_30-34" 16317
170 "005" 1998 "170005_1998_35-39" 8888
170 "005" 1998 "170005_1998_40-44" 2205
170 "005" 1998 "170005_1998_45-49" 167
170 "005" 1998 "170005_1998_50-54" 20
170 "005" 1998 "170005_1998_Sin informacion" 774
170 "008" 1998 "170008_1998_10-14" 170
170 "008" 1998 "170008_1998_15-19" 6776
170 "008" 1998 "170008_1998_20-24" 11185
170 "008" 1998 "170008_1998_25-29" 10067
170 "008" 1998 "170008_1998_30-34" 6336
170 "008" 1998 "170008_1998_35-39" 2792
170 "008" 1998 "170008_1998_40-44" 490
170 "008" 1998 "170008_1998_45-49" 51
170 "008" 1998 "170008_1998_50-54" 3
170 "008" 1998 "170008_1998_Sin informacion" 510
170 "011" 1998 "170011_1998_10-14" 606

It seems easy, but I can't find the solution. Thank you in advance for your help!!

[Carlo Lazzaro]

Federica:
I'm not sure I got you right.
Therefore, please consider what follows ("005" group only) as a temptative reply:

. g wanted= births_count if key=="170005_1998_Sin informacion"

. quietly sum wanted

. replace wanted=r(min)

. list

     +----------------------------------------------------------------------------------+
     | raw_co~y   raw_ge~1   raw_ye~r                           key   births~t   wanted |
     |----------------------------------------------------------------------------------|
  1. |      170        005       1998             170005_1998_10-14        756      774 |
  2. |      170        005       1998             170005_1998_15-19      21667      774 |
  3. |      170        005       1998             170005_1998_20-24      26981      774 |
  4. |      170        005       1998             170005_1998_25-29      21990      774 |
  5. |      170        005       1998             170005_1998_30-34      16317      774 |
     |----------------------------------------------------------------------------------|
  6. |      170        005       1998             170005_1998_35-39       8888      774 |
  7. |      170        005       1998             170005_1998_40-44       2205      774 |
  8. |      170        005       1998             170005_1998_45-49        167      774 |
  9. |      170        005       1998             170005_1998_50-54         20      774 |
 10. |      170        005       1998   170005_1998_Sin informacion        774      774 |
     +----------------------------------------------------------------------------------+


.

[Federica Becca]

Thank you Carlo! it works, however I need to do it for 23 years, each with 33 Department and 9 age-classes. How can I do it in a loop or for all the observation? I can't use ...
if key=="170005_1998_Sin informacion" and repeat it for all the other observations, there are more than 6000 rows.

I also have the variable raw_mothage, and with <gen wanted= births_count if raw_mothage==999> I could get this, but now I don't know how to replace the "." with the same value that I have in Sin informacion.

* Example generated by -dataex-. To install: ssc install dataex clear input float raw_country str3 raw_geolev1 double raw_yearbir long raw_mothage str28 key float births_count byte _merge float wanted
170 "005" 1998 1 "170005_1998_10-14" 756 1 . 170 "005" 1998 2 "170005_1998_15-19" 21667 1 .
170 "005" 1998 3 "170005_1998_20-24" 26981 1 . 170 "005" 1998 4 "170005_1998_25-29" 21990 1 .
170 "005" 1998 5 "170005_1998_30-34" 16317 1 . 170 "005" 1998 6 "170005_1998_35-39" 8888 1 .
170 "005" 1998 7 "170005_1998_40-44" 2205 1 . 170 "005" 1998 8 "170005_1998_45-49" 167 1 .
170 "005" 1998 9 "170005_1998_50-54" 20 1 . 170 "005" 1998 999 "170005_1998_Sin informacion" 774 3 774
170 "005" 1999 1 "170005_1999_10-14" 864 1 . 170 "005" 1999 2 "170005_1999_15-19" 23142 1 .
170 "005" 1999 3 "170005_1999_20-24" 28419 1 . 170 "005" 1999 4 "170005_1999_25-29" 21657 1 .
170 "005" 1999 5 "170005_1999_30-34" 16371 1 . 170 "005" 1999 6 "170005_1999_35-39" 9049 1 .
170 "005" 1999 7 "170005_1999_40-44" 2447 1 . 170 "005" 1999 8 "170005_1999_45-49" 169 1 .
170 "005" 1999 9 "170005_1999_50-54" 22 1 . 170 "005" 1999 999 "170005_1999_Sin informacion" 783 3 783
170 "005" 2000 1 "170005_2000_10-14" 965 1 . 170 "005" 2000 2 "170005_2000_15-19" 22808 1 .

[Nick Cox]

* Example generated by -dataex-. To install: ssc install dataex
clear
input float raw_country str3 raw_geolev1 double raw_yearbir str28 key float births_count
170 "005" 1998 "170005_1998_10-14" 756
170 "005" 1998 "170005_1998_15-19" 21667
170 "005" 1998 "170005_1998_20-24" 26981
170 "005" 1998 "170005_1998_25-29" 21990
170 "005" 1998 "170005_1998_30-34" 16317
170 "005" 1998 "170005_1998_35-39" 8888
170 "005" 1998 "170005_1998_40-44" 2205
170 "005" 1998 "170005_1998_45-49" 167
170 "005" 1998 "170005_1998_50-54" 20
170 "005" 1998 "170005_1998_Sin informacion" 774
170 "008" 1998 "170008_1998_10-14" 170
170 "008" 1998 "170008_1998_15-19" 6776
170 "008" 1998 "170008_1998_20-24" 11185
170 "008" 1998 "170008_1998_25-29" 10067
170 "008" 1998 "170008_1998_30-34" 6336
170 "008" 1998 "170008_1998_35-39" 2792
170 "008" 1998 "170008_1998_40-44" 490
170 "008" 1998 "170008_1998_45-49" 51
170 "008" 1998 "170008_1998_50-54" 3
170 "008" 1998 "170008_1998_Sin informacion" 510
end  

bysort raw_* (key) : gen wanted = births_count[_N] 

list, sepby(raw_*) 

     +----------------------------------------------------------------------------------+
     | raw_co~y   raw_ge~1   raw_ye~r                           key   births~t   wanted |
     |----------------------------------------------------------------------------------|
  1. |      170        005       1998             170005_1998_10-14        756      774 |
  2. |      170        005       1998             170005_1998_15-19      21667      774 |
  3. |      170        005       1998             170005_1998_20-24      26981      774 |
  4. |      170        005       1998             170005_1998_25-29      21990      774 |
  5. |      170        005       1998             170005_1998_30-34      16317      774 |
  6. |      170        005       1998             170005_1998_35-39       8888      774 |
  7. |      170        005       1998             170005_1998_40-44       2205      774 |
  8. |      170        005       1998             170005_1998_45-49        167      774 |
  9. |      170        005       1998             170005_1998_50-54         20      774 |
 10. |      170        005       1998   170005_1998_Sin informacion        774      774 |
     |----------------------------------------------------------------------------------|
 11. |      170        008       1998             170008_1998_10-14        170      510 |
 12. |      170        008       1998             170008_1998_15-19       6776      510 |
 13. |      170        008       1998             170008_1998_20-24      11185      510 |
 14. |      170        008       1998             170008_1998_25-29      10067      510 |
 15. |      170        008       1998             170008_1998_30-34       6336      510 |
 16. |      170        008       1998             170008_1998_35-39       2792      510 |
 17. |      170        008       1998             170008_1998_40-44        490      510 |
 18. |      170        008       1998             170008_1998_45-49         51      510 |
 19. |      170        008       1998             170008_1998_50-54          3      510 |
 20. |      170        008       1998   170008_1998_Sin informacion        510      510 |
     +----------------------------------------------------------------------------------+