Create ratios of asset values by different groups and graph the change in ratios across different waves (panel data)

[Chris Boulis]

I would like to graph the proportion of asset values (for different asset types) between different groups (each group represents a type of household based on characteristics, such as race, religion, etc.) over age. The variable 'group' is coded 1-9 with each category representing a different type of household grouping (each collated using value labels). For example, the ratio of property assets of an indigenous population as a share of the total population. Each asset type is valued in dollar terms and is a continuous variable.

I have panel data and the key data is contained in every fourth wave. Each household in each grouping is distinguished by a couple id (a -group-ing of id & p_id). Here's some sample data from waves 10 to 18:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float couple byte(wave group) double(totasset totbank totprop)
 5 10 1   81080   4694  350000
 5 11 1       0      0       0
 5 12 1       0      0       0
 5 13 1       0      0       0
 5 14 1  313100   8100  450000
 5 15 1       0      0       0
 5 16 1       0      0       0
 5 17 1       0      0       0
 5 18 1  320038    960  550000
10 10 4 1014100 310000  605000
10 11 4       0      0       0
10 12 4       0      0       0
10 13 4       0      0       0
10 14 4 1212400 228400  889000
10 15 4       0      0       0
10 16 4       0      0       0
10 17 4       0      0       0
10 18 4 1245400 270400 1089000
14 10 5  542600   2075  500525
14 11 5       0      0       0
14 12 5       0      0       0
14 13 5       0      0       0
14 14 5  622655    355  600000
14 15 5       0      0       0
14 16 5       0      0       0
14 17 5       0      0       0
14 18 5  550592   4200  450000
21 10 3  374095     90  340000
21 11 3       0      0       0
21 12 3       0      0       0
21 13 3       0      0       0
21 14 3  374095     90  340000
21 15 3       0      0       0
21 16 3       0      0       0
21 17 3       0      0       0
21 18 3  867100   9100  750000
end

I've run the following code (is based on feedback from here). The first section appears to be working, but there appears to be an issue in the second part, where I am getting '15s' when I would expect proportions of around 0.4 to 0.6 on average.

// create initial groupings - This code seems to work.

tempfile ratios
foreach x in totasset totbank totprop {
gen `x'1    = `x' if inlist(group, 1) // x asset value for group 1
gen `x'3    = `x' if inlist(group, 3) // x asset value for group 3
gen `x'4    = `x' if inlist(group, 4) //  x asset value for group 4
gen `x'5    = `x' if inlist(group, 5) //  x asset value for group 5
gen `x'345  = `x' if inlist(group, 3, 4, 5) //  x asset value for groups 3 + 4 + 5
gen `x'34   = `x' if inlist(group, 3, 4) //  x asset value for groups 3 + 4
gen `x'35   = `x' if inlist(group, 3, 5) //  x asset value for groups 3 + 5
gen `x'45   = `x' if inlist(group, 4, 5) //  x asset value for groups 4 + 5
gen `x'13   = `x' if inlist(group, 1, 3) //  x asset value for groups 1 + 3
gen `x'14   = `x' if inlist(group, 1, 4) //  x asset value for groups 1 + 4
gen `x'15   = `x' if inlist(group, 1, 5) //  x asset value for groups 1 + 5

}

collapse (sum) totasset* totbank* totprop* (median) medtotasset=totasset medtotbank=totbank medtotprop=totprop, by(couple wave)

save `ratios'

// merge with main dataset
use "`savingdir'/`filename'" // filepath of main dataset
merge 1:1 couple wave using `ratios', update
drop _merge
save "`savingdir'/`filename'", replace

// create ratios - There appears a problem with this code.

forval j = 1/7 {
      gen rel`j' = .  
      
// create ratios
foreach x in totasset* totbank* totprop* {   // I'm not sure if this correctly call the variables created above, i.e. `x'1:`x'15?
replace rel`j' = `x'3 / `x'345 // group 3 / groups 3, 4, 5
replace rel`j' = `x'3 / `x'34 // group 3 / groups 3 + 4
replace rel`j' = `x'3 / `x'35 // group 3 / groups 3 + 5
replace rel`j' = `x'4 / `x'45 // group 4 / groups 4 + 5
replace rel`j' = `x'1 / `x'13 // group 1 / groups 1 + 3
replace rel`j' = `x'1 / `x'14 // group 1 / groups 1 + 4
replace rel`j' = `x'1 / `x'15 // group 1 / groups 1 + 5

    }
}

* end goal - graph the ratios over the lifecycle (age).

Stata v.15.1. Using panel data with multiple waves.

[Chris Boulis]

Based on advice in #9 here by Ken Chui, I removed the forval loop as well as the inclusion of the * wildcard after each variable in my second foreach statement such that my code for "create ratios" is now:

foreach x in totasset totbank totprop {
gen rel1`x' = `x'3 / `x'345 // group 3 / groups 3, 4, 5
gen rel2`x' = `x'3 / `x'34 // group 3 / groups 3, 4
gen rel3`x' = `x'3 / `x'35 // group 3 / groups 3, 5
gen rel4`x' = `x'4 / `x'45 // group 4 / groups 4, 5
gen rel5`x' = `x'1 / `x'13 // group 1 / groups 3
gen rel6`x' = `x'1 / `x'14 // group 1 / groups 1, 4
gen rel7`x' = `x'1 / `x'15 // group 1 / groups 1, 5
}

The ratios obtained for rel1* to rel7* are '0' or '1', though I believe this is because I am -collapsing- by couple and as such, the summing is being calculated with respect to each couple, which is not what I want.

collapse (sum) totasset* totbank* totprop* ///
(median) medtotasset=totasset medtotbank=totbank medtotprop=totprop, by(couple wave)

I believe the summing should be for all people/couples in each category of group (shown as groups 1 - 5 in the above code). That said, if I remove 'couple(ID)' from the -by()- option what will I merge on when I merge the collapsed data to the main dataset?

Are there any users that can offer some advice please?