Generating a New Variable Across Levels

[Davia Downey]

Hello Statalisters,

I have a dataset and am trying to create a new variable across different levels. What I would like to calculate is the proportion of Total Debt Outstanding by the level of General Revenues. The catch is that I want this data by year and level of government in the data set. I think this might need to happen in two phases, but I'm not quite sure how to proceed. The variables of interest are Year, Name (Type Code will also work), General Revenue-Own Sources, and Total Debt Outstanding. I've attached a data snippet below. The goal is to create a table that shows the corresponding debt ratios across time for each level of government. Note that the Census of Governments (where this data is derived) changed its classification of Municipalities and Cities, which is why Townships and Cities are listed separately in certain years. They do represent different types of sub-state governments, so I'd like to preserve that data when it appears in the table.

* Example generated by -dataex-. For more info, type help dataex
clear
input int Year byte TypeCode str18 Name long(GenRevOwnSources TotalDebtOutstanding)
1940 2 "US STATE GOVTS"       3657000    3590000
1940 3 "US LOCAL TOTAL"       5007000   16693000
1940 0 "NATIONAL TOTAL"      14858000   63251000
1940 4 "FEDERAL GOVERNMENT"   6194000   42968000
1950 2 "US STATE GOVTS"       8839000    5285000
1950 3 "US LOCAL TOTAL"       9586000   18830000
1950 0 "NATIONAL TOTAL"      58486000  281472000
1950 4 "FEDERAL GOVERNMENT"  40061000  257357000
1961 2 "US STATE GOVTS"      21911200   19993200
1961 3 "US LOCAL TOTAL"      24995300   55030000
1961 0 "NATIONAL TOTAL"     133968500  363994200
1961 4 "FEDERAL GOVERNMENT"  87062000  288971000
1970 2 "US STATE GOVTS"      57506700   42007700
1970 3 "US LOCAL TOTAL"      51419900  101588500
1970 0 "NATIONAL TOTAL"     272508700  514515100
1970 4 "FEDERAL GOVERNMENT" 163582000  370919000
1980 2 "US STATE GOVTS"     169265645  121957862
1980 3 "US LOCAL TOTAL"     130027382  213645193
1980 6 "US CITIES"           47785892   86018793
1980 7 "US TOWNSHIPS"         5899320    4871455
1980 0 "NATIONAL TOTAL"     716629027 1249919055
1980 4 "FEDERAL GOVERNMENT" 417336000  914316000
1981 2 "US STATE GOVTS"     187373487  134846537
1981 3 "US LOCAL TOTAL"     145735821  229045344
1981 6 "US CITIES"           53406532   91301905
1981 7 "US TOWNSHIPS"         6341256    5377995
1981 0 "NATIONAL TOTAL"     820814308 1367830881
1981 4 "FEDERAL GOVERNMENT" 487705000 1003939000
1982 2 "US STATE GOVTS"     205945387  147470100
1982 3 "US LOCAL TOTAL"     164426048  257108946
1982 6 "US CITIES"           59642958   98420773
1982 7 "US TOWNSHIPS"         6717578    5567670
1982 0 "NATIONAL TOTAL"     866392435 1551565046
1982 4 "FEDERAL GOVERNMENT" 496021000 1146986000
end

[Hemanshu Kumar]

Is this what you had in mind?

. gen prop = TotalDebtOutstanding / GenRevOwnSources
. labmask TypeCode, values(Name)
. table (TypeCode) (Year), stat(mean prop) nformat(%4.3f) nototals

-----------------------------------------------------------------------------
                     |                           Year                        
                     |   1940    1950    1961    1970    1980    1981    1982
---------------------+-------------------------------------------------------
TypeCode             |                                                       
  NATIONAL TOTAL     |  4.257   4.813   2.717   1.888   1.744   1.666   1.791
  US STATE GOVTS     |  0.982   0.598   0.912   0.730   0.721   0.720   0.716
  US LOCAL TOTAL     |  3.334   1.964   2.202   1.976   1.643   1.572   1.564
  FEDERAL GOVERNMENT |  6.937   6.424   3.319   2.267   2.191   2.058   2.312
  US CITIES          |                                  1.800   1.710   1.650
  US TOWNSHIPS       |                                  0.826   0.848   0.829
-----------------------------------------------------------------------------

where I use the community-contributed command labmask (available from the Stata Journal) to give value labels to TypeCode, in turn so that the list of levels of government is in the order determined by TypeCode.

[George Ford]

bys Year TypeCode: egen num = sum(TotalDebtOutstanding)
bys Year TypeCode: egen den = sum(GenRevOwnSources)
g newvar = num / den

* or

collapse (sum) GenRevOwnSources TotalDebtOutstanding (first) Name , by(Year TypeCode)
g newvar = TotalDebtOutstanding / GenRevOwnSources

[Hemanshu Kumar]

A quick note on #3:

On the data extract, I ran

isid Year TypeCode

which confirmed that there was just one observation per combination of Year and TypeCode. Hence I did not collapse or generate the sums as in #3. If this does not hold for the full dataset, then I would suggest first collapsing as in #3, and then making the table as I suggested in #2.

[Davia Downey]

I've never heard of the labmask function. This is exactly what I was looking for. Thank you both.

[Nick Cox]

The labmask command was written up in

SJ-8-2 gr0034 . . . . . . . . . . Speaking Stata: Between tables and graphs
(help labmask, seqvar if installed) . . . . . . . . . . . . N. J. Cox
Q2/08 SJ 8(2):269--289
outlines techniques for producing table-like graphs

https://journals.sagepub.com/doi/pdf...867X0800800208