Weighted Mean Based on Importance

[Drew Nelson]

Hi all,

I'm looking to calculate, by row, and weighted mean based on importance. Importance being determined by the payer mix proportion each respective payer accounts for out of our total payers (payers meaning insurance companies). So, for example, 'ABC_Commercial' accounts for 60% of our payer mix, 'ABC_Medicare_Advantage' accounts for 15% of our payer mix, and 'EFG_Commercial' accounts for the remaining 25%. By DCode, i'd like to calculate an overall weighted average for each code that factors in the percentage of each payor (the result would be a new column with the weighted average by DCode). I've gone down a few rabbit holes with ASGEN and iweight but it doesn't seem like either meet my needs. Any help would be much appreciated. Dataex below:




input str14 DCode float(ABC_Commercial ABC_Medicare_Advantage EFG_Commercial)
"10060" 122.14317 82.39 101.4625
"10061" 184.51195 124.46 151.775
"10120" 162.15585 109.38 132.075
"10121" 913.2645 616.03 807.975

[Nick Cox]

If I understand this correctly the weights are just constants in a linear combination and there is no need for anything except

gen wanted = 0.6 * ABC_Commercial + 0.15 * ABC_Medicare_Advantage + 0.25 * EFG_Commercial

[Drew Nelson]

Thanks, Nick. I think I was over complicating things in my head.