Summing multiple variables

[Nick Richardson]

Is there a way to sum multiple variables just using the prefix? In other words, id like to tell stata to sum all the variables that share the same prefix.
For example, I have variables called "recid_charges_prop1.....recid_charges_prop15)
I didn't see anything when browsing through the egen help file.

Of course, they are not in order in the data set.

[Clyde Schechter]

Any Stata command that takes a varlist enables you to specify certain wild cards. See -help varlist- for full details. In your case, if these 15 are the only variables with the prefix recid_charges_prop, you could get the sum with:

egen total_recid_charges = rowtotal(recid_charges_prop*)

If there are other variables with the same prefix but these 15 are located consecutively in the data set, then you can do

egen total_recid_charges = rowtotal(recid_charges_prop1 - recid_charges_prop15)

If, however, these variables are scattered around the data set and there are more of them than just the 15 you are currently interested in, then wildcards won't help you. In that case, I would reorder the variables (-help order-) to get these adjacent.

[Nick Richardson]

Thanks! the first one is what I was looking for. Unfortunately they were not located consecutively.

[Hend She]

Since this relates to my issue, I am posting it here. The variables that I want to sum up are located consecutively, however, when I run the syntax above I get an error that 'varlist not allowed'.

. egen p_transfers = rowtotal (iunay - istuy) / 12

varlist not allowed
r(101);

[Clyde Schechter]

The error message is misleading. The varlist is allowed. What's not allowed is the /12 that comes afterward. -egen-, unlike -gen-, does not allow expressions, or allows them only in limited contexts. You have to break this up into two commands:

egen p_transfers = rowtotal(iunay-istuy)
replace p_transfers = p_transfers/12

[Nick Cox]

If what you want is the row mean, then use that function in egen. (It's smarter about missing values when they exist...)

[Hend She]

Thanks a lot! yes, I was surprised about the varlist not allowed message!

[Philmon Amasalu]

Hello,
I came across this discussion while searching for how I can add multiple rows by id and year.
Is there a way to use 'egen' as mentioned in #2? My variables have same prefix as mentioned above. I would like to take the missing values into account.
One long way is to add each variable (close to 70 variables all with the suffix Adj).


Here is how my data looks:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long GeoFips int years float(Adjoag1 Adjsupportming1 Adjconstbuilding1 Adjspecialtrcont1 Adjwoods1 Adjnonmetal1 Adjprimary1 Adjmachine1 Adjelectroniceqt1 Adjfoods1 Adjtextmills1 Adjlethers1 Adjpapers1)
1000 1969  516.1592  374.5688 24669.355 31141.746  20346.83  8729.617  45363.89  6094.577  3301.619 26448.203  14684.57   .  16967.49
1000 1970  480.9665    349.03  22722.77  30066.23  19454.21  8843.402  44137.62  6158.495 3821.9624 26889.184 14659.516   . 17488.787
1000 1971 531.80035  385.9194 23117.135  31113.16 19130.986  8771.081  42986.14  5797.387  3652.717 25656.574  14392.69   .  17570.09
1000 1972 604.42017  438.6185 25472.807 35848.004  20225.45  9583.969  43560.49    5647.7  3855.582 26932.895 15510.758   .  18164.08
1000 1973 608.33044  441.4561 30461.785  39324.96 21000.783  9275.399  43712.85  6401.601 3962.5544 26552.135 16350.285   . 18293.209
1000 1974  621.7372  451.1852 32207.506  41405.02  20213.18  9682.325  43132.03  7685.417  4281.943  26189.83 16251.364   . 18740.855
1000 1975  689.3295  500.2358 30668.957  40678.48  19521.72  9016.973 38293.414    6926.6  4176.881 25033.955 14858.334   . 18160.254
1000 1976  808.3142 586.58124  30836.17   42246.9  21242.18  9520.326 38037.633   6809.69  4895.123  25236.96  15929.22   . 18847.027
1000 1977   853.562  619.4168 33103.504  45380.79 22560.994  10429.64  39739.46  7306.281  5300.088     26760  15454.14   .  19016.33
1000 1978 1030.0839  747.5161 34162.504  50574.04 23585.904  10760.39  41728.46  8145.952  6272.389  26950.38 14908.771   .  18753.29
1000 1979  1248.502  906.0187 33861.734  48661.57 24942.906 10646.604   41579.8   8898.76  7184.708 27445.754  14421.65   . 19625.625
1000 1980 1323.3562  960.3393  32985.72  44871.13  23920.72     10177 36492.785  8774.203  7007.822 27711.896 13995.703   .  19175.11
1000 1981  1570.822 1139.9215 28242.826  43590.56 21951.365  9916.645 34597.043  8752.896  7289.389 27681.785 13631.908   . 20260.746
1000 1982 1454.0718 1055.1976  25302.44  40715.94  20304.55  9035.294  27571.81  7766.816  7427.688  26358.84  12526.53   .  20561.09
1000 1983 1535.6294 1114.3828  24526.33  44725.23 22293.004  9421.005 23135.797  8327.325    8263.6  26283.08 12813.206   . 20961.865
1000 1984  1760.751   1277.75 28241.773  48159.79  24194.85   9555.04  24863.48  9906.692  8956.244 27395.244 12813.532   .  20544.83
1000 1985  1832.812 1330.0436  30465.99  52002.48  24085.06  9829.859 24601.234 10963.793 8790.4375  28309.26 11844.496   .  20987.69
1000 1986 1454.6304  1055.603 32804.836  55169.89  23824.57 10340.928  23389.73 11192.694   8609.73  29232.99  12264.91   . 20406.135
1000 1987         .         .     29896  60001.34 22715.105   9976.43  23620.58 12138.895  8678.498 30343.215 12753.008   . 20513.264
1000 1988         .         . 32314.773  61884.24  22888.31  9914.716  25035.23     14659  7598.461  31615.65 13251.843   .  20705.52
1000 1989         .         . 30488.076  63419.15 22946.955  9530.933  24879.18 15406.344  7458.634 32880.316 13341.979   .  21048.91
1000 1990         .         .  31338.85  67789.24  22920.36  8856.902 24592.924 15747.784  7659.588  34210.06  13372.24   .  21377.95
1000 1991         .         .  30091.61  67769.53 22115.707  8301.478 23705.535 15804.054  7544.211  34994.89  13511.51   .  21283.25
1000 1992 1785.8884  1295.992  29771.91  71109.45  23014.46  8307.263  23483.92 14909.753  7872.769  35677.73 14366.657   . 21088.125
1000 1993         .         . 29963.307  75176.91 24732.877  8245.549 22953.887 15164.877  8240.677  36570.38 14820.588   .  20975.26
1000 1994  1818.288 1319.5038  30145.24  78048.57  25894.17  8838.581 23487.613 15581.708  7960.257 36830.695 13313.018   .  20787.78
1000 1995         .         .  31105.39  84435.63 27549.166  9235.863 24107.215 16173.902  8390.056 37109.465 13506.955   .   20393.7
1000 1996         .         .  32756.46  90219.38  28474.52  9458.612  24194.94 16873.172  8634.945  36562.61 13115.176   .  20293.27
1000 1997         .         . 33900.645  94134.04  29021.41   9762.36  24662.18 17424.941  8396.168 36146.883 13067.343   . 20323.875
1000 1998      1804      1579     35912     96671     28789      9604     25030     16444      7950     36576     12650   .     20397
1000 1999      1752      1260     36979     98633     29149      9415     24501     16319      7965     38012     12746   .     19123
1000 2000      1636       977     37196     99992     26781      9397     22898     16179      7776     37390     12496   .     18107
1000 2001      1864       965     39363     99189     24055      9348     21234     14658      7371     37041     12235 189     17069
1000 2002      1715       948     37855     95092     23241      8997     19415     13339      6523     36891     11909 219     15055
1000 2003      1986       882     35462    101106     21576      8695     18698     12647      6092     35749     11436 198     15082
1000 2004      1833       982     36967    106561     21687      8687     18513     13035      6123     35732     10960 195     14912
1000 2005      2021      1124     40082    111042     21915      8862     19168     13447      6249     35986     10802 221     14371
1000 2006      2264      1199     41096    115867     22476      9454     19149     14390      6017     35239      9424 219     14312
1000 2007      2355      1357     41572    119350     21306     10298     18465     13961      5684     34901      8236 167     14087
1000 2008      3212      1377     39066    114359     18833      9363     18034     13323      5480     35052      5874 172     13789
1000 2009      3140      1144     34058    100026     13947      7776     15646     11378      5128     33718      4555 145     12834
1000 2010      4002      1133     31714     92602     13518      7188     16485     10692      4957     32883      4833 144     12553
1000 2011      3459      1216     29428     90926     13883      7040     17876     11337      4914     32472      4803 183     12439
1000 2012      4334      1470     30519     87677     14044      6771     18476     11347      5284     32237      4063 214     12165
1000 2013      4953      1415     30508     87006     14570      6885     18324     11272      5370     32598      4100 138     12224
1000 2014      4816      1361     29680     87344     15159      6793     19122     11120      5234     31736      3964 142     11478
1000 2015      4930      1312     31912     85832     15931      7020     18195     11531      5138     32035      3952 155     11198
1000 2016      5062      1098     32082     90386     16730      7281     17210     11255      5270     32851      4080 160     11228
1000 2017      3109      1303     31515     90638     17082      7551     16970     11251      5252     33816      3960 167     11531
1000 2018      2543      1244     33355     93920     18069      7699     17404     11366      5568     34312      4070 185     11871
1000 2019      2186      1160     34804     96994     18303      7782     17819     11242      5700     34481      4231 182     11732
1000 2020      2032      1062     34209     96260     18341      7626     16918     10283      6021     33633      3602 164     11355
2000 1969         .         . 2551.5596  2982.945  677.9339 234.43147         .         .         .   3289.18         .   .         .
2000 1970         .         .  2867.962   3242.59  732.2965  235.3095         .         .         . 3817.6355         .   .         .
2000 1971         .         .  3160.634  3485.388  733.4957  273.0644         .         .         . 3679.9536         .   .         .
2000 1972         .         .  3527.887 3723.2305  758.2786  244.0897         .         .         . 3814.5984         .   .         .
2000 1973         .         .  3552.747 3909.5405  914.1714 263.40616         .         .         .  4665.999         .   .         .
2000 1974         .         .  5037.579  5245.424 1003.7099  290.6248         .         .         . 4372.4126         .   .         .
2000 1975         .         .  5457.942  6904.377  924.5643  381.9389         .         .         . 4398.7344         .   .         .
2000 1976         .         .   5172.05  7183.843  873.3995  372.2807         .         .         .  5216.727         .   .         .
2000 1977         .         .  4829.657  7491.056  927.7621  308.1852         .         .         .  5583.204         .   .         .
2000 1978         .         .  5120.069  6834.016  796.2526 298.52698         .         .         .  6472.062         .   .         .
2000 1979         .         . 4257.8726  5974.809  927.3624 266.04022         .         .         .  7243.485         .   .         .
2000 1980         .         .    3897.4  5932.195 1075.2607 289.74677         .         .         .  8153.603         .   .         .
2000 1981         .         .  5964.186  6610.047  934.9572  367.8906         .         .         .  8295.334         .   .         .
2000 1982  3202.647  5737.335  7365.396  9207.488  898.9819  380.1829         .         .         .  7145.286         .   .         .
2000 1983  2970.908  5322.188  9360.992  11197.44   813.041  421.4499         .         .         .  6508.507         .   .         .
2000 1984 3268.2886  5854.927  10382.52 12239.983  770.2704  464.4729         .         .         .  5922.346         .   .         .
2000 1985  3537.019  6336.341  8868.309 11907.995   765.074  492.5695         .         .         .  6529.767         .   .         .
2000 1986 3384.3396  6062.825  6667.051  8839.823  830.2292  524.1783         .         .         .  6973.183         .   .         .
2000 1987 3171.4585  5681.462  4947.178  7803.225   1090.85   554.909         .         .         .  7054.172         .   .         .
2000 1988  3341.546  5986.163 4609.3057  6941.045 1229.9543  535.5925         .         .         .   8819.74         .   .         .
2000 1989  3562.043  6381.169  4209.282   7259.16  1332.284 545.25073         .         .         .  9027.275         .   .         .
2000 1990  3896.778  6980.825 4197.9824  7799.261 1413.4282  462.7168         .         .         . 10048.754         .   .         .
2000 1991  4084.998  7318.009 4407.0337  8204.585  1233.552  420.5718         .         .         .  11074.28         .   .         .
2000 1992 3592.1436  6435.092  4430.764  8838.832 1114.8335  402.1334         .         .         . 11867.978         .   .         .
2000 1993  3488.786  6249.933  4922.318  9262.985 1130.0231  402.1334         .         .         . 11194.753         .   .         .
2000 1994  3604.837  6457.831  4825.137 10216.338 1092.8486  520.6662         .         .         . 10895.093         .   .         .
2000 1995 3386.5156  6066.723  5276.011 10545.354 1101.2428  431.9861         .         .         . 11151.222         .   .         .
2000 1996  3148.611  5640.532  5436.472 11288.612  984.9228  462.7168         .         .         . 10658.198         .   .         .
2000 1997 3093.4866  5541.781  5584.503 11575.015  996.1151 439.01025         .         .         . 10315.006         .   .         .
2000 1998      3388      6413      6101     11814       936       401         .         .         .      9681         .   .         .
2000 1999      3187      4988      6205     12097       786       427         .         .         .      8838         .   .         .
2000 2000      3031      5877      6094     12072       726       424         .         .         .      8830         .   .         .
2000 2001      3287      6695      6410     12587       759       438         .        77        26      9402        55   .         .
2000 2002      3081      6129      6801     13154       595       469         .        90        23      8150        83   .         .
2000 2003      2903      5665      7328     13907       575       495         .        83        24      8585         .   .         .
2000 2004      2829      5864      7533     14568       673       479         .        67        33      9243        91   .         .
2000 2005      2934      6355      8006     15141       668       489         .        96        40      9390       108   .         2
2000 2006      3207      7638      7770     14669         .         .         .        96        40     10065       114   .         .
2000 2007      3565      8688      7715     14581       715       445         .       103        44      9941       114   .         .
2000 2008      4057      9682      7251     14358       682       429        57       121        37      9706        99   .         .
2000 2009      4086      9386      6882     13681       617       470        61       135        47      9796       114  55         .
2000 2010      4187      9407      6632     13251         .       454        59       120         .      9798       114   .         .
2000 2011      4164      9569      6515     13147         .       482        50       147         .     10837       124   .         .
2000 2012      4628     10073      6510     13356         .       473        45       134         .     10944       109  89         .
2000 2013      4918     10233      6411     13307         .       455        42       135        50     11215       135   .         .
2000 2014      5064     10776      6353     13363       728       420        52       128         .     11330       170  67         .
2000 2015      4969     10367      6578     13134       670       463        40       164        38     10906       177  87         .
2000 2016      4701      7860      6147     12799       750       460        31       138        39     10541       144  90         .
end

[Clyde Schechter]

First you speak of adding rows by id and year. Then you speak of adding the Adj* variables--which would be adding "colulmns" (in the spreadsheet jargon). So I can't figure out what you want. Please clarify exactly what you want to add to what, and how you want the results organized. And explain just how you want to take the missing values into account: treat them as zero? treat them as the average of the non-missing ones in the same observation (row) or variable (column)? Something else?

[Philmon Amasalu]

Hi Clyde, Sorry for that. I wanted the total industrial employment (adjvar1-adjvar70) by state and year.
It must be the "column" sums.
I want the missing values to be considered as zero.

[Clyde Schechter]

OK. That's very straightforward

egen wanted = rowtotal(Adj*)

I don't know that the "by state and year" part amounts to anything: from your example data it appears that you already have exactly one observation per state and year, so there is no further aggregation to be done.

[Philmon Amasalu]

Thank you Clyde Schechter ,

My long code:

foreach v of varlist Adjoag1 -Adjfunds{
replace `v'=0 if mi(`v')
}

egen stotal1=total(Adjoag1 +Adjsupportming1+ Adjconstbuilding1+ Adjspecialtrcont1+ Adjwoods1+ Adjnonmetal1+ Adjprimary1+ Adjmachine1+ ///
Adjelectroniceqt1+ Adjfoods1+ Adjtextmills1+ Adjlethers1+ Adjpapers1+ Adjprinters1+ Adjpetros1 +Adjchemicals1+ Adjplastics1+ Adjdurables1+ ///
Adjnondurables1+ Adjmotvehc1+ Adjhomfurns1 +Adjfoodbevs1+ Adjmerchandise1+ Adjnonstorretailer1+ Adjairt1+Adjrail1+ Adjwater1+ Adjtrucks1+ ///
Adjtransitground1+ Adjutil1+ Adjmotpics1+ Adjbroadcasting1+ Adjtelecomms1+ Adjdataprocs1+ Adjmonauthors1+ Adjsecurity1+ Adjrealestate1+ ///
Adjlessors1 +Adjeduc1+ Adjambulatory1+ Adjhospital1+ Adjsocass1+ Adjmuseum1+ Adjamusement1+ Adjaccomod1+ Adjfoodserv1+ Adjmembership1+ ///
Adjheavycivilengi1+ Adjapperel1+ Adjbuldingmaterial1+Adjhealthpcare1+ Adjclothing1 +Adjinternetpub1+ Adjadminsupport+ Adjperformarts+ ///
Adjperslaundry+ Adjsports+ Adjcouriers+ Adjpublish+ Adjcredits+ Adjrepairmaintenance+ Adjcomputerelectronics+ Adjotherinfo+ Adjfunds+Adjgasoline+ ///
Adjinssurance+ Adjnursing+ Adjfabricatedmprods+ Adjpipeline+ Adjfurnitures+ Adjbevertobacco+ Adjelectronicapps), by(GeoFips year)

and your

egen stotal2=rowtotal(Adj*)
assert stotal1==stotal2

yield the same result. The amount of time it takes to type my code is not comparable to yours. I also suspect that a 0 value in my code is equivalent to a missing value. I am not sure how they affects my results but I am afraid.
I am using your without replacing missing values with zero.
Thank you for your help!

[Clyde Schechter]

Actually, a zero in your code is not equivalent to a missing value. If any of the Adj* variables were missing, your code wold give missing value for the end result.