Summing observations based on unique id for different age category

[Tariq Abdullah]

Hello,

i have to find grand total for each unique unitid for the following age category.

The efage09 variable give the grand total of students for each year. this is data of each students in a unique academic insitituion (unitid) for different age category. I want to get the grand total of students for each unitid based on the following 8 age categories:

Age category

1. efbage 3 "Age under 18" + efbage 4 "Age 18-19"
2. efbage 5 "Age 20-21" + efbage 6 "Age 22-24"
3. efbage 8 "Age 25-29",
4. efbage 9 "Age 30-34"
5. efbage 10 "Age 35-39"
6. efbage 11 "Age 40-49
7. efbage 12 "Age 50-64"
8. efbage 13 "Age 65 and over"

* Example generated by -dataex-. For more info, type help dataex
clear
input long unitid byte(efbage lstudy) long efage09
100654  1 1  6076
100654  1 2  4978
100654  1 5  1098
100654  2 1  4386
100654  2 2  4094
100654  2 5   292
100654  3 1   943
100654  3 2   941
100654  3 5     2
100654  4 1  1699
100654  4 2  1698
100654  4 5     1
100654  5 1   889
100654  5 2   846
100654  5 5    43
100654  6 1   855
100654  6 2   609
100654  6 5   246
100654  7 1  1335
100654  7 2   529
100654  7 5   806
100654  8 1   548
100654  8 2   251
100654  8 5   297
100654  9 1   260
100654  9 2   103
100654  9 5   157
100654 10 1   164
100654 10 2    57
100654 10 5   107
100654 11 1   262
100654 11 2    93
100654 11 5   169
100654 12 1    99
100654 12 2    25
100654 12 5    74
100654 13 1     2
100654 13 5     2
100654 14 1   355
100654 14 2   355
100663  1 1 16561
100663  1 2 11284
100663  1 5  4302
100663  1 6   975
100663  2 1  9537
100663  2 2  8202
100663  2 5   858
100663  2 6   477
100663  3 1    11
100663  3 2    11
100663  4 1  2326
100663  4 2  2326
100663  5 1  3051
100663  5 2  3026
100663  5 5    14
100663  5 6    11
100663  6 1  4149
100663  6 2  2839
100663  6 5   844
100663  6 6   466
100663  7 1  7024
100663  7 2  3082
100663  7 5  3444
100663  7 6   498
100663  8 1  3477
100663  8 2  1503
100663  8 5  1539
100663  8 6   435
100663  9 1  1478
100663  9 2   658
100663  9 5   767
100663  9 6    53
100663 10 1   789
100663 10 2   366
100663 10 5   417
100663 10 6     6
100663 11 1   899
end
label values efbage label_efbage
label def label_efbage 1 "All age categories total", modify
label def label_efbage 2 "Age under 25 total", modify
label def label_efbage 3 "Age under 18", modify
label def label_efbage 4 "Age 18-19", modify
label def label_efbage 5 "Age 20-21", modify
label def label_efbage 6 "Age 22-24", modify
label def label_efbage 7 "Age 25 and over total", modify
label def label_efbage 8 "Age 25-29", modify
label def label_efbage 9 "Age 30-34", modify
label def label_efbage 10 "Age 35-39", modify
label def label_efbage 11 "Age 40-49", modify
label def label_efbage 12 "Age 50-64", modify
label def label_efbage 13 "Age 65 and over", modify
label def label_efbage 14 "Age unknown", modify
label values lstudy label_lstudy
label def label_lstudy 1 "All Students total", modify
label def label_lstudy 2 "Undergraduate", modify
label def label_lstudy 5 "Graduate", modify
label def label_lstudy 6 "First professional", modify

[Tariq Abdullah]

For me keep if lstudy == 1 should be starting block , since I just want to find out "All Students total" in each unique unitid for 8 mentioned age categories

[Tariq Abdullah]

This is what I did but I guess i need to line them in one single row for each unitid. I'm not sure how do i do that ?

* Create variables for each age group
gen age_under_18_19 = cond(inlist(efbage, 3, 4), efage09, 0)
gen age_20_24 = cond(inlist(efbage, 5, 6), efage09, 0)
gen age_25_29 = cond(efbage == 8, efage09, 0)
gen age_30_34 = cond(efbage == 9, efage09, 0)
gen age_35_39 = cond(efbage == 10, efage09, 0)
gen age_40_49 = cond(efbage == 11, efage09, 0)
gen age_50_64 = cond(efbage == 12, efage09, 0)
gen age_65_over = cond(efbage == 13, efage09, 0)

* Calculate the grand total for each unitid
egen grand_total = rowtotal(age_under_18_19 age_20_24 age_25_29 age_30_34 age_35_39 age_40_49 age_50_64 age_65_over)

[Nick Cox]

Here is one of various simpler approaches, if I understand correctly what you want.


.

 egen wanted = total(efage09), by(efbage unitid)

. tabdisp efbage unitid, c(wanted)

-----------------------------------------
                         |     unitid    
                  efbage | 100654  100663
-------------------------+---------------
All age categories total |  12152   33122
      Age under 25 total |   8772   19074
            Age under 18 |   1886      22
               Age 18-19 |   3398    4652
               Age 20-21 |   1778    6102
               Age 22-24 |   1710    8298
   Age 25 and over total |   2670   14048
               Age 25-29 |   1096    6954
               Age 30-34 |    520    2956
               Age 35-39 |    328    1578
               Age 40-49 |    524     899
               Age 50-64 |    198        
         Age 65 and over |      4        
             Age unknown |    710        
-----------------------------------------

Various tabulation commands will get you a table even more directly, but this method gives you a variable that you may want to use for other purposes.

. tab efbage unitid [fw=efage09]

                      |        unitid
               efbage |    100654     100663 |     Total
----------------------+----------------------+----------
All age categories to |    12,152     33,122 |    45,274 
   Age under 25 total |     8,772     19,074 |    27,846 
         Age under 18 |     1,886         22 |     1,908 
            Age 18-19 |     3,398      4,652 |     8,050 
            Age 20-21 |     1,778      6,102 |     7,880 
            Age 22-24 |     1,710      8,298 |    10,008 
Age 25 and over total |     2,670     14,048 |    16,718 
            Age 25-29 |     1,096      6,954 |     8,050 
            Age 30-34 |       520      2,956 |     3,476 
            Age 35-39 |       328      1,578 |     1,906 
            Age 40-49 |       524        899 |     1,423 
            Age 50-64 |       198          0 |       198 
      Age 65 and over |         4          0 |         4 
          Age unknown |       710          0 |       710 
----------------------+----------------------+----------
                Total |    35,746     97,705 |   133,451 

.

[Tariq Abdullah]

Mr. Cox,

thank you so much! that defnitely was the easier way to tackle this problem. Much obliged for your time !