summing the multiplication of variables by stored coefficients via a loop

[Daniel Sutcliffe]

Hello,

In the sample data below, I have ethnic categories, that take any value between 0 and 1, and I have the values from age splines (asp*). I want to manually create a variable that stores the effect of the age*ethnicity interaction using the coefficients from an OLS regression mode, for females only which is the base group.

This is my code:

estimates use coef.ster

cap drop pred_ethnicity_interact
gen pred_ethnicity_interact = 0

local ethnicities bangladeshi chinese indian pakistani other_asian african caribbean other_black white_and_asian white_and_black_african white_and_black_carabbean other_mixed white_irish

foreach ethnicities in `ethnicities' {
forvalues i = 0/18 {

replace pred_ethnicity_interact = pred_ethnicity_interact + (_b[c.`ethnicities'#c.asp`i'] * (asp`i'*`ethnicities'))
}


}

replace pred_ethnicity_interact = 0 if male !=0



This is the name of the coefficients stored in the .ster file: (_b[c.`ethnicities'#c.asp`i'

This is the interaction of the the asp variables and the ethnicity variable: (asp`i'*`ethnicities'))

I am not sure if my code is right, as when I do an example in excel to check if the code is working, I get similar but different results.

Please can someone advise?

N.B. I included asp0-6 and only some of the ethnicities to cut down the size of the sample data for posting.

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 der_nhs_number double(bangladeshi chinese indian pakistani other_asian white_irish white_other any_other_ethnic_group) str2 person_level_ethnicity long agecat byte(asp0 asp1 asp2 asp3 asp4 asp5 asp6) float male
"100013244646"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  11 1 4 5 5 5 5 5 1
"100010850813"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   6 1 4 5 5 5 3 0 0
"100018720474"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  16 1 4 5 5 5 5 5 1
"100000932663"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  11 1 4 5 5 5 5 5 1
"100040033209"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  13 1 4 5 5 5 5 5 0
"100004043159"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  16 1 4 5 5 5 5 5 0
"100059754852" .000767459708365311  .00153491941673062 .00537221795855718 .00460475825019187  .00613967766692249 .00153491941673062 .00920951650038373 .00767459708365311 "9"   4 1 4 5 4 0 0 0 0
"100030314600"                   0                   0                  0                  0                   1                  0                  0                  0 "L"  10 1 4 5 5 5 5 5 0
"100006291146"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  17 1 4 5 5 5 5 5 1
"100010528872"                   0                   0                  0                  1                   0                  0                  0                  0 "J"  16 1 4 5 5 5 5 5 0
"100038270507"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  16 1 4 5 5 5 5 5 0
"100014499986"                   0                   0                  1                  0                   0                  0                  0                  0 "H"  19 1 4 5 5 5 5 5 0
"100064653453"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   3 1 4 0 0 0 0 0 0
"100658567288"                   0                   0                  0                  0                   0                  0                  0                  0 "G"   1 0 0 0 0 0 0 0 1
"100022287436"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   5 1 4 5 5 3 0 0 1
"100059058709"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   9 1 4 5 5 5 5 5 1
"100003503710"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  11 1 4 5 5 5 5 5 1
"100002413343"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  12 1 4 5 5 5 5 5 0
"100038026726"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  18 1 4 5 5 5 5 5 1
"100013321358"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  19 1 4 5 5 5 5 5 0
"100024639340"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  14 1 4 5 5 5 5 5 1
"100400214592"   .0132871172732525   .0150202195262854 .00808781051415367   .121317157712305   .0375505488157135 .00231080300404391  .0473714615829001   .024841132293472 "10"  2 1 0 0 0 0 0 0 0
"100051753042"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   5 1 4 5 5 3 0 0 0
"100041520468"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  13 1 4 5 5 5 5 5 0
"100021421492"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  14 1 4 5 5 5 5 5 0
"100045862688"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   5 1 4 5 5 4 0 0 0
"100020066080"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  13 1 4 5 5 5 5 5 1
"100658640499"                   0                   0                  0                  0                   0                  0                  0                  0 "M"   7 1 4 5 5 5 5 4 0
"100002094226"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  11 1 4 5 5 5 5 5 1
"100413211331"                   0                   0                  0                  0                   0                  0                  0                  0 "G"   1 0 0 0 0 0 0 0 0
"100016608301"                   0                   0                  1                  0                   0                  0                  0                  0 "H"  12 1 4 5 5 5 5 5 0
"100064010419"                   0 .000833333333333333                  0                .04  .00583333333333333 .00333333333333333              .0175 .00583333333333333 "9"  15 1 4 5 5 5 5 5 0
"100027332530"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   8 1 4 5 5 5 5 5 1
"100044022741"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  19 1 4 5 5 5 5 5 0
"100002645081"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  17 1 4 5 5 5 5 5 0
"100037717065"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  12 1 4 5 5 5 5 5 1
"100007447071"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  14 1 4 5 5 5 5 5 0
"100054946611"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   3 1 4 1 0 0 0 0 1
"100070554259"  .00912584053794428  .00384245917387128 .00864553314121038   .487512007684918   .0216138328530259 .00144092219020173  .0561959654178674  .0465898174831892 "Z"  14 1 4 5 5 5 5 5 0
"100005608783"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  10 1 4 5 5 5 5 5 0
"100013976910"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  15 1 4 5 5 5 5 5 0
"100034909510"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   9 1 4 5 5 5 5 5 0
"100003496400"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   9 1 4 5 5 5 5 5 1
"100027159591"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   4 1 4 5 1 0 0 0 0
"100055973906"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  15 1 4 5 5 5 5 5 1
"100024670363"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  13 1 4 5 5 5 5 5 0
"100005613389"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  10 1 4 5 5 5 5 5 0
"100047778264"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  16 1 4 5 5 5 5 5 1
"100065118980"  .00912584053794428  .00384245917387128 .00864553314121038   .487512007684918   .0216138328530259 .00144092219020173  .0561959654178674  .0465898174831892 "Z"   4 1 4 5 3 0 0 0 1
"100017327501"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   4 1 4 5 4 0 0 0 0
"100021244385"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  13 1 4 5 5 5 5 5 1
"100037374954"                   0                   0                  0                  1                   0                  0                  0                  0 "J"   3 1 4 4 0 0 0 0 0
"100044084405"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  15 1 4 5 5 5 5 5 0
"100018135504"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   8 1 4 5 5 5 5 5 1
"100018579748"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  18 1 4 5 5 5 5 5 0
"100035185287"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  17 1 4 5 5 5 5 5 0
"100026097899"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   6 1 4 5 5 5 0 0 0
"100031156972"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  12 1 4 5 5 5 5 5 0
"100000094820"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  11 1 4 5 5 5 5 5 0
"100005602531"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  12 1 4 5 5 5 5 5 1
"100037445571"                   0                   0                  0                  0                   0                  0                  1                  0 "C"   4 1 4 5 2 0 0 0 1
"100048843354"                   0  .00362100181050091  .0114665057332529  .0229330114665057  .00663850331925166  .0024140012070006 .00663850331925166  .0229330114665057 "Z"   5 1 4 5 5 1 0 0 1
"100044909869"  .00075642965204236   .0037821482602118 .00453857791225416 .00605143721633888                   0 .00226928895612708 .00907715582450832 .00302571860816944 "Z"  15 1 4 5 5 5 5 5 1
"100053348290"                   0                   0                  0                  1                   0                  0                  0                  0 "J"   3 1 4 1 0 0 0 0 1
"100070312214"                   0                   0                  0                  1                   0                  0                  0                  0 "J"   2 1 1 0 0 0 0 0 0
"100030800981"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  10 1 4 5 5 5 5 5 1
"100040095543"  .00339943342776204  .00113314447592068  .0028328611898017  .0543909348441926  .00736543909348442 .00623229461756374 .00793201133144476 .00509915014164306 "9"  14 1 4 5 5 5 5 5 0
"100002282599"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   7 1 4 5 5 5 5 1 0
"100005603945"                   0                   0                  0                  1                   0                  0                  0                  0 "J"  10 1 4 5 5 5 5 5 0
"100055303668"                   0                   0                  0                  0                   0                  0                  0                  0 "G"   3 1 4 0 0 0 0 0 1
"100022017658"                   0                   0                  0                  0                   0                  0                  0                  0 "N"   4 1 4 5 3 0 0 0 0
"100030927548"                   0                   0                  0                  0                   0                  0                  0                  0 "F"   4 1 4 5 0 0 0 0 1
"100005289137"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   4 1 4 5 4 0 0 0 1
"100005607474"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  12 1 4 5 5 5 5 5 0
"100067804196"   .0319351763584366 .000953288846520496  .0386081982840801   .198284080076263   .0328884652049571 .00714966634890372  .0514775977121068  .0467111534795043 "99"  7 1 4 5 5 5 5 1 1
"100030189285"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   4 1 4 5 4 0 0 0 0
"100039663035"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  19 1 4 5 5 5 5 5 1
"100016882147"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   6 1 4 5 5 5 1 0 0
"100017448215"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   7 1 4 5 5 5 5 1 0
"100035183421"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  18 1 4 5 5 5 5 5 0
"100004940046"                   0                   0                  0                  1                   0                  0                  0                  0 "J"  12 1 4 5 5 5 5 5 0
"100018132881"                   0                   0                  0                  1                   0                  0                  0                  0 "J"   9 1 4 5 5 5 5 5 0
"100043017004"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   9 1 4 5 5 5 5 5 0
"100056814505"   .0102622576966933   .0125427594070696 .00912200684150513   .189281641961231   .0307867730900798 .00266058532877233  .0414291144051691  .0387685290763968 "9"   8 1 4 5 5 5 5 5 0
"100017222028"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   5 1 4 5 5 0 0 0 1
"100028106357"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  16 1 4 5 5 5 5 5 0
"100050753570"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   6 1 4 5 5 5 2 0 0
"100005600064"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  13 1 4 5 5 5 5 5 0
"100005620849"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  10 1 4 5 5 5 5 5 0
"100031651003"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  17 1 4 5 5 5 5 5 1
"100061712234"                   0  .00471976401179941 .00294985250737463 .00648967551622419 .000589970501474926 .00471976401179941 .00943952802359882                  0 "9"  12 1 4 5 5 5 5 5 1
"100030904679"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   4 1 4 5 0 0 0 0 1
"100005326310"                   0                   0                  0                  1                   0                  0                  0                  0 "J"   5 1 4 5 5 4 0 0 0
"100030743436"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   6 1 4 5 5 5 1 0 1
"100046264548"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  17 1 4 5 5 5 5 5 0
"100027710574"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  18 1 4 5 5 5 5 5 1
"100010846473"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   6 1 4 5 5 5 4 0 0
"100020067741"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  13 1 4 5 5 5 5 5 1
"100054735197"                   0                   0                  0                  0                   0                  0                  0                  0 "A"  12 1 4 5 5 5 5 5 1
"100030789412"                   0                   0                  0                  0                   0                  0                  0                  0 "A"   4 1 4 5 4 0 0 0 1
end
label values agecat agecat
label def agecat 1 "a0", modify
label def agecat 2 "a1_4", modify
label def agecat 3 "a5_9", modify
label def agecat 4 "a10_14", modify
label def agecat 5 "a15_19", modify
label def agecat 6 "a20_24", modify
label def agecat 7 "a25_29", modify
label def agecat 8 "a30_34", modify
label def agecat 9 "a35_39", modify
label def agecat 10 "a40_44", modify
label def agecat 11 "a45_49", modify
label def agecat 12 "a50_54", modify
label def agecat 13 "a55_59", modify
label def agecat 14 "a60_64", modify
label def agecat 15 "a65_69", modify
label def agecat 16 "a70_74", modify
label def agecat 17 "a70_79", modify
label def agecat 18 "a80_84", modify
label def agecat 19 "a85plus", modify

------------------ copy up to and including the previous line ------------------

Listed 100 out of 9238604 observations
Use the count() option to list more

[Clyde Schechter]

I want to manually create a variable that stores the effect of the age*ethnicity interaction using the coefficients from an OLS regression mode

It depends on what you mean by "the effect of the age*ethnicity interaction." If by that you mean the grand total of the values of the interaction terms involving asp1 through asp18 with one of the ethnicities listed in your local macro ethnicities, then the code looks like it will work correctly.

Even so, there is one thing I would change. You originally define local macro ethnicities by listing out the ethnicities of interest. Your subsequent -foreach- statement then uses the same name, ethnicities, for the iterator of the loop. Consequently, the original definition of ethnicities is overwritten once you start the loop. Since you don't seem to use, nor need to use, the full list any more, this doesn't lead to any incorrect results. But it is a bad practice to use the same name to refer to two different things. And if subsequently you needed to change the code and use the full list of ethnicities again, you would have to recreate it. So I recommend changing that loop to:

foreach ethnicity in `ethnicities' {
    forvalues i = 0/18 {

        replace pred_ethnicity_interact = pred_ethnicity_interact + (_b[c.`ethnicity'#c.asp`i'] * (asp`i'*`ethnicity'))
    }
}

Also, the "variable" ped_ethnicity_interact that you are calculating is actually a constant: it takes on the same value in every observation. So there probably is no reason to make it a variable, and it would be more sensible to make it a scalar or local macro instead.

All of that said, totaling up the interaction terms like this strikes me as unusual--I've never seen anybody do that and I wonder what purpose it serves. Perhaps, then, the grand total is not really what you want--in which case to fix your code you would have to spell out exactly what it is you do want.

[Daniel Sutcliffe]

Thanks Clyide, The variable I need to create needs to be the sum of the interaction terms across involving asp1 through asp18 with all the ethnicities. In the data, someone has an ethnicity based on a probability - where we know the ethnicity of the person the value is 1 for that ethnicity and 0 for all the others, where ethnicity is not known, it is a probability, and therefore someone has a value between 0 and 1 for a number of ethnicities. As far as I can see, the code works where the ethnicity is 1 and and zero for all others, but not in the cases where someone has a probability against a number of ethnicities.

[Clyde Schechter]

Well, to troubleshoot your code, you need to also provide that coefficient vector. Actually, easier than doing that would be to just provide the regression command that you used--that can be run on your example data to create the _b[] vector.