Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Generating a New Variable Across Levels

    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.

    Code:
    * 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

  • #2
    Is this what you had in mind?

    Code:
    . 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.
    Last edited by Hemanshu Kumar; 21 Aug 2025, 11:28.

    Comment


    • #3
      Code:
      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

      Comment


      • #4
        A quick note on #3:

        On the data extract, I ran
        Code:
        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.

        Comment


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

          Comment


          • #6
            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

            Comment

            Working...
            X