Ratio of two variables

[Ludmila Farooq]

Hello
In the data shown below I have following variables:
County
Year
pop1= Population of children age 1year
pop2= Population of children age 2years
Children_tot= total number of children enrolled in kindergarten
Children_1year= number of children 1year old enrolled in kindergarten
Children_2year= number of children 2years enrolled in kindergarten
I want to calculate the share (over time) of children of age 1 and two who are enrolled in kindergarten by the total population of their own age.
I also want to calculate the share of total children who are enrolled in kindergarten from the population of children in all ages (here i and 2 years)

Please help
Thanks

* Example generated by -dataex-. To install: ssc install dataex
clear
input str16 County int Year str4(pop1 pop2) str5 Children_tot str4(Children_1year Children_2year)
"Halden"      1995 "323" "322" "901"  "38"  "76" 
"Halden"      1996 "316" "328" "903"  "31"  "66" 
"Halden"      1997 "295" "322" "832"  "38"  "88" 
"Halden"      1998 "347" "308" "855"  "39"  "94" 
"Halden"      1999 "334" "354" "924"  "51"  "116"
"Halden"      2000 ""    ""    "952"  "45"  "102"
"Halden"      2001 "310" "314" "931"  "44"  "92" 
"Halden"      2002 "334" "306" "934"  "39"  "104"
"Halden"      2003 "335" "343" "1022" "67"  "130"
"Halden"      2004 "325" "332" "1043" "67"  "134"
"Halden"      2005 "306" "327" "1159" "111" "150"
"Halden"      2006 "328" "308" "1235" "112" "223"
"Halden"      2007 "290" "339" "1213" "142" "181"
"Halden"      2008 "300" "297" "1239" "136" "215"
"Halden"      2009 "314" "309" "1369" "196" "245"
"Halden"      2010 "327" "319" "1414" "240" "259"
"Moss"        1995 "330" "322" "689"  "43"  "83" 
"Moss"        1996 "287" "333" "722"  "48"  "92" 
"Moss"        1997 "353" "283" "780"  "55"  "112"
"Moss"        1998 "324" "351" "873"  "56"  "142"
"Moss"        1999 "334" "338" "938"  "69"  "147"
"Moss"        2000 ""    ""    "957"  "62"  "107"
"Moss"        2001 "351" "339" "967"  "62"  "128"
"Moss"        2002 "324" "350" "1052" "86"  "156"
"Moss"        2003 "332" "322" "1085" "107" "167"
"Moss"        2004 "336" "340" "1179" "120" "192"
"Moss"        2005 "297" "337" "1261" "157" "210"
"Moss"        2006 "322" "306" "1262" "150" "227"
"Moss"        2007 "321" "326" "1372" "211" "243"
"Moss"        2008 "367" "325" "1476" "244" "294"
"Moss"        2009 "357" "370" "1465" "192" "284"
"Moss"        2010 "341" "366" "1525" "248" "265"
"Sarpsborg"   1995 "537" "579" "1187" "103" "151"
"Sarpsborg"   1996 "550" "548" "1270" "100" "166"
"Sarpsborg"   1997 "574" "544" "1415" "158" "226"
"Sarpsborg"   1998 "540" "580" "1518" "146" "271"
"Sarpsborg"   1999 "580" "569" "1512" "115" "234"
"Sarpsborg"   2000 ""    ""    "1554" "130" "235"
"Sarpsborg"   2001 "606" "573" "1589" "127" "234"
"Sarpsborg"   2002 "558" "622" "1624" "134" "226"
"Sarpsborg"   2003 "603" "561" "1669" "144" "270"
"Sarpsborg"   2004 "593" "619" "1978" "201" "326"
"Sarpsborg"   2005 "583" "618" "2195" "230" "352"
"Sarpsborg"   2006 "537" "594" "2367" "261" "382"
"Sarpsborg"   2007 "571" "549" "2395" "300" "394"
"Sarpsborg"   2008 "568" "577" "2470" "353" "433"
"Sarpsborg"   2009 "616" "583" "2598" "370" "498"
"Sarpsborg"   2010 "631" "628" "2677" "397" "492"
"Fredrikstad" 1995 "847" "780" "1884" "153" "283"
"Fredrikstad" 1996 "803" "835" "1986" "171" "276"
"Fredrikstad" 1997 "794" "817" "2081" "209" "328"
"Fredrikstad" 1998 "827" "826" "2112" "160" "359"
"Fredrikstad" 1999 "844" "831" "2148" "181" "328"
"Fredrikstad" 2000 ""    ""    "2211" "177" "314"
"Fredrikstad" 2001 "828" "807" "2247" "179" "357"
"Fredrikstad" 2002 "840" "835" "2396" "170" "373"
"Fredrikstad" 2003 "770" "872" "2625" "217" "363"
"Fredrikstad" 2004 "819" "793" "2792" "246" "429"
"Fredrikstad" 2005 "779" "828" "2956" "290" "449"
"Fredrikstad" 2006 "797" "814" "3160" "357" "517"
"Fredrikstad" 2007 "803" "800" "3385" "426" "574"
"Fredrikstad" 2008 "799" "833" "3505" "452" "635"
"Fredrikstad" 2009 "802" "827" "3679" "491" "644"
"Fredrikstad" 2010 "793" "833" "3827" "596" "662"
"Hvaler"      1995 "36"  "31"  "71"   "5"   "12" 
"Hvaler"      1996 "34"  "38"  "90"   "4"   "11" 
"Hvaler"      1997 "36"  "39"  "91"   "3"   "9"  
"Hvaler"      1998 "38"  "35"  "96"   "6"   "10" 
"Hvaler"      1999 "35"  "40"  "92"   "2"   "14" 
"Hvaler"      2000 ""    ""    "93"   "6"   "10" 
"Hvaler"      2001 "44"  "32"  "117"  "7"   "22" 
"Hvaler"      2002 "32"  "46"  "124"  "9"   "17" 
"Hvaler"      2003 "34"  "33"  "134"  "6"   "21" 
"Hvaler"      2004 "22"  "40"  "144"  "20"  "10" 
"Hvaler"      2005 "42"  "23"  "132"  "8"   "26" 
"Hvaler"      2006 "27"  "39"  "130"  "12"  "15" 
"Hvaler"      2007 "37"  "30"  "159"  "25"  "29" 
"Hvaler"      2008 "44"  "41"  "180"  "21"  "38" 
"Hvaler"      2009 "32"  "49"  "165"  "25"  "28" 
"Hvaler"      2010 "36"  "30"  "199"  "32"  "40" 
"Aremark"     1995 "22"  "15"  "66"   "4"   "8"  
"Aremark"     1996 "23"  "21"  "66"   "3"   "7"  
"Aremark"     1997 "19"  "22"  "60"   "5"   "6"  
"Aremark"     1998 "17"  "18"  "57"   "3"   "9"  
"Aremark"     1999 "9"   "17"  "59"   "2"   "6"  
"Aremark"     2000 ""    ""    "50"   "4"   "4"  
"Aremark"     2001 "19"  "11"  "40"   "5"   "5"  
"Aremark"     2002 "18"  "19"  "55"   "7"   "7"  
"Aremark"     2003 "20"  "19"  "62"   "3"   "15" 
"Aremark"     2004 "21"  "19"  "65"   "3"   "9"  
"Aremark"     2005 "12"  "21"  "70"   "3"   "6"  
"Aremark"     2006 "20"  "14"  "67"   "7"   "11" 
"Aremark"     2007 "15"  "20"  "63"   "3"   "9"  
"Aremark"     2008 "15"  "17"  "59"   "6"   "9"  
"Aremark"     2009 "11"  "15"  "65"   "8"   "8"  
"Aremark"     2010 "15"  "12"  "61"   "7"   "11" 
"Marker"      1995 "45"  "34"  "116"  "1"   "5"  
"Marker"      1996 "24"  "46"  "117"  "0"   "3"  
"Marker"      1997 "35"  "22"  "114"  "6"   "21" 
"Marker"      1998 "41"  "38"  "112"  "0"   "15" 
end

[Friedrich Huebler]

First you have to convert the string variables to numeric variables.

destring pop1 - Children_2year, replace

Then you sum up the population in each year with collapse. Watch out for missing values that can introduce errors in the calculations that follow. The data excerpt indicates that the total population of 1- and 2-year-olds is missing for the year 2000 for all municipalities.

help collapse

The last step is to calculate the proportion of children who are enrolled in kindergarten with basic arithmetic.