rescaling population by group using a loop

Daniel Sutcliffe

Join Date: Sep 2020
Posts: 77

rescaling population by group using a loop

01 Aug 2022, 10:19

Hello,

I need to rescale a set of populations by a group total.

I can do this individually with some code I have written as follows:

qui sum gp_comb_wtd_pop_hi_adj_1 if ICB22CD =="QHM"
local PopTot = r(sum)
di r(sum)
qui sum comb_wtd_pop_hi_adj_1 if ICB22CD =="QHM"
local RegPatTot = r(max)
di r(max)
gen gp_comb_wtd_pop_hi_adj_1_nm = gp_comb_wtd_pop_hi_adj_1 /(`PopTot')*(`RegPatTot') if ICB22CD =="QHM"

However, the group variable is ICB22CD and there are 42 of these (only 4 in the sample data). It seems like it would be inefficient to write the above code 42 times for each of the 42 codes. I was wondering if there was some way to incorporate the above into a loop, but only create one output variable "gen gp_comb_wtd_pop_hi_adj_1_nm" (rather than one for each of the 42 ICB22CD codes)

Can anyone advise please?

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str3 ICB22CD double(gp_comb_wtd_pop_hi_adj_1 comb_wtd_pop_hi_adj_1 random)
"QKS"  4356.188928639295 2009537.7474208532 .00041316583816486396
"QHM"   8816.16065934225  3194894.959481737    .00101701538010468
"QHM" 13111.922949317763  3194894.959481737  .0023669310626158735
"QHM"  5241.080441829006  3194894.959481737   .004326024467743328
"QKS"  6037.285356598347 2009537.7474208532   .006043274668028009
"QRL" 14022.222416882027 1862385.6205456927   .006550137116878907
"QHM"  6875.102967680193  3194894.959481737  .0065860863223848876
"QHM"  5387.819505862622  3194894.959481737   .008269031486953016
"QHM"  8770.357923428175  3194894.959481737   .008853671310333433
"QRL"  548.0749927231357 1862385.6205456927   .009382504835864114
"QRL" 13694.376908445991 1862385.6205456927   .010030057088346145
"QHM"  6285.813720804144  3194894.959481737    .01057567834518569
"QHM"  5137.674918175068  3194894.959481737   .013446643798276203
"QNQ"   12994.7587871539  764158.3943161273    .01448431990636767
"QHM"  7501.716135334695  3194894.959481737   .014864396471098718
"QKS" 13391.086983207115 2009537.7474208532   .017851671844532624
"QKS"  6916.212647580345 2009537.7474208532   .020878164823272427
"QHM" 11238.246618356497  3194894.959481737    .02453706812941714
"QHM"  4823.577337291417  3194894.959481737    .02468827371511939
"QHM" 11499.919720080048  3194894.959481737   .024948438575294607
"QHM"  5357.003501917952  3194894.959481737    .02502181570576323
"QHM" 2193.1147173043596  3194894.959481737   .025262335482994502
"QHM"  5088.916193963542  3194894.959481737   .026803572951164756
"QKS" 17380.262245615904 2009537.7474208532    .02768541168568761
"QRL"  2296.875316880689 1862385.6205456927   .028097987913581313
"QKS" 18572.841827365435 2009537.7474208532   .030816227437729382
"QNQ"  10446.12012802075  764158.3943161273    .03194396300165547
"QHM" 11656.237366676585  3194894.959481737    .03210575697754947
"QNQ" 3348.7048965448867  764158.3943161273    .03245087337680641
"QKS"  2943.908436500107 2009537.7474208532   .034111152271153355
"QHM" 13753.445765598044  3194894.959481737    .03514513040335998
"QNQ"  9118.937802894181  764158.3943161273   .037937491973015214
"QHM"  8330.012672615358  3194894.959481737      .039593616020554
"QKS" 13561.904468458099 2009537.7474208532    .04250407947825663
"QHM"  11563.20827251521  3194894.959481737    .04268131156667765
"QHM"  8455.794816686226  3194894.959481737   .042761563846746564
"QKS" 14217.531001057712 2009537.7474208532   .046135376116141935
"QKS"  8707.288077883808 2009537.7474208532   .046293781898895436
"QHM"  9124.244411993357  3194894.959481737   .046657578820748946
"QHM"   3473.51978155177  3194894.959481737    .04751085518932452
"QRL" 14269.869591107123 1862385.6205456927    .04768013930820836
"QNQ"  6151.545681933913  764158.3943161273    .04783112201564277
"QHM"  6809.331218093224  3194894.959481737    .04811303208962381
"QRL"  9500.106827739888 1862385.6205456927    .04911697183713948
"QHM"   5899.14089395063  3194894.959481737   .053040920890057186
"QHM"  5873.688994970919  3194894.959481737    .05315559769458067
"QHM"  10472.32224716786  3194894.959481737    .05518824463982763
"QNQ" 11751.862672584482  764158.3943161273    .05640913640406875
"QHM" 4038.3592824814136  3194894.959481737    .05781260660108456
"QHM" 13635.417567320002  3194894.959481737   .058066715934210866
"QKS" 10516.590092467104 2009537.7474208532   .058282211405244255
"QNQ"  5991.768528491536  764158.3943161273    .05892478977695281
"QHM"  7918.506019882021  3194894.959481737    .06121660053588285
"QHM"  9424.129879557702  3194894.959481737    .06210930010686655
"QHM"  6363.144474509743  3194894.959481737    .06263053900180238
"QHM"  9258.819924072359  3194894.959481737    .06387911437185301
"QHM" 15219.921714054586  3194894.959481737    .06427531846321999
"QRL"  5526.137864470369 1862385.6205456927     .0662972858343841
"QRL" 14558.922116383752 1862385.6205456927    .06655987781324291
"QKS"  6417.143493748127 2009537.7474208532     .0665862081624845
"QHM"  9876.533363752696  3194894.959481737    .06802183423893438
"QRL" 39063.575524481574 1862385.6205456927    .07011672814431058
"QKS"  8099.030892898863 2009537.7474208532    .07068851691503364
"QNQ" 5228.2691136505755  764158.3943161273    .07090757816337001
"QKS" 12043.779226659939 2009537.7474208532    .07382504394117373
"QHM" 20988.040225985158  3194894.959481737    .07396643725924623
"QRL"  4913.637823282171 1862385.6205456927    .07909981215135542
"QRL"  .7373228853219554 1862385.6205456927    .08071855471113609
"QNQ" 10603.402289810803  764158.3943161273    .08116170713479287
"QHM" 15349.377629995153  3194894.959481737    .08128520917197257
"QHM" 11944.319637208986  3194894.959481737     .0822971973993466
"QRL" 15525.077477525263 1862385.6205456927    .08290308147965686
"QKS"  9677.583462283215 2009537.7474208532    .08344631756671295
"QHM"  5126.103416646388  3194894.959481737    .08490014000245505
"QHM"  8787.284306238334  3194894.959481737    .08542711945325909
"QHM"  5929.409421923865  3194894.959481737    .08839074535547453
"QNQ" 10712.893708113712  764158.3943161273    .08842172882914989
"QRL" 11884.603095203453 1862385.6205456927    .08849710534676392
"QKS" 13754.267579695352 2009537.7474208532    .08862547212556537
"QHM"  4862.494137837806  3194894.959481737    .09044493989355795
"QHM" 1755.7770790855889  3194894.959481737    .09122455396448903
"QHM" 14846.960632847342  3194894.959481737    .09637839971482276
"QRL"   2794.97898925748 1862385.6205456927    .09718310363761173
"QHM"  4731.388808068297  3194894.959481737    .09916107429774501
"QHM" 6698.6968095800585  3194894.959481737    .10467806902307297
"QKS"  5747.233745960941 2009537.7474208532    .10566003085130238
"QRL" 14989.355580948757 1862385.6205456927    .10574618128265822
"QHM" 10465.315888367859  3194894.959481737    .10594403608214609
"QHM" 10258.509963168925  3194894.959481737    .10653710506748382
"QHM"  9915.599036033831  3194894.959481737    .10937033879762559
"QKS"  3207.486328443174 2009537.7474208532    .10996908954208162
"QKS"  8666.502450413545 2009537.7474208532     .1101878309811769
"QHM"  7550.390507480596  3194894.959481737    .11071719240169886
"QHM"  4700.809400502289  3194894.959481737     .1117535921898255
"QHM"  12480.50621733235  3194894.959481737    .11176541366671444
"QHM"  6609.858119977091  3194894.959481737    .11211246364473826
"QHM"  6673.939094091182  3194894.959481737    .11296131211000493
"QHM" 13470.406440304709  3194894.959481737    .11378921613780901
"QRL"   8791.52358698529 1862385.6205456927    .11391140147039047
"QNQ" 13319.600526660004  764158.3943161273    .11529752000651838
end

Last edited by Daniel Sutcliffe; 01 Aug 2022, 10:23.

Tags: None

Clyde Schechter

Join Date: Apr 2014
Posts: 30355

01 Aug 2022, 10:26

Code:

gen gp_comb_wtd_pop_hi_adj_1_nm = .
levelsof ICB22CD, local(icbs)
foreach i of local icbs {
    qui sum gp_comb_wtd_pop_hi_adj_1 if ICB22CD =="`i'"
    local PopTot = r(sum)
    di r(sum)
    qui sum comb_wtd_pop_hi_adj_1 if ICB22CD =="`i'"
    local RegPatTot = r(max)
    di r(max)
    replace gp_comb_wtd_pop_hi_adj_1_nm = ///
        gp_comb_wtd_pop_hi_adj_1 /(`PopTot')*(`RegPatTot') if ICB22CD =="`i'"
}

Comment

William Lisowski

Join Date: Dec 2014
Posts: 10150

01 Aug 2022, 11:15

I think this loopless version accomplishes the same goal - at least on the example data, within the limits of rounding.

Code:

generate seq = _n
bysort ICB22CD: egen pt  = total(gp_comb_wtd_pop_hi_adj_1)
bysort ICB22CD: egen rpt = max(comb_wtd_pop_hi_adj_1)
sort seq
drop seq
generate gp_comb_wtd_pop_hi_adj_1_nm = gp_comb_wtd_pop_hi_adj_1 /pt * rpt

Announcement

rescaling population by group using a loop

Comment

Comment