Evaluation of the gap between total monetary expenditures and total monetary income
Source: Household Survey Georgia
List of command P. Castel

IN Individual file (with data on occupation of each member of the household)

mark the hh that have a member in each WAGE EMPLOYEMENT sector. 

B3 indicates the wage employment sector 
uid is the number of the hh for a particular quarter

{com}. egen BB1=sum (B3==1), by(uid)
{txt}
{com}. egen BB2=sum (B3==2), by(uid)
{txt}
{com}. egen BB3=sum (B3==3), by(uid)
{txt}
{com}. egen BB4=sum (B3==4), by(uid)
{txt}
{com}. egen BB5=sum (B3==5), by(uid)
{txt}
{com}. egen BB6=sum (B3==6), by(uid)
{txt}
{com}. egen BB7=sum (B3==7), by(uid)
{txt}
{com}. egen BB8=sum (B3==8), by(uid)
{txt}
{com}. egen BB9=sum (B3==9), by(uid)
{txt}
{com}. egen BB10=sum (B3==10), by(uid)
{txt}
{com}. egen BB11=sum (B3==11), by(uid)
{txt}
{com}. egen BB12=sum (B3==12), by(uid)
{txt}
{com}. egen BB14=sum (B3==13), by(uid)
{txt}
{com}. egen BB14=sum (B3==14), by(uid)
{txt}
{com}. egen BB15=sum (B3==15), by(uid)
{txt}
{com}. egen BB16=sum (B3==16), by(uid)
{txt}
{com}. save "P:\GEORGIA\PREM\Poverty-update\1CP\data\all-data\IND.dta", replace
{txt}file P:\GEORGIA\PREM\Poverty-update\1CP\data\all-data\IND.dta saved

{com}. sort uid
{txt}
{com}. collapse BB1 BB2 BB3 BB4 BB5 BB6 BB7 BB8 BB9 BB10 BB11 BB12 BB13 BB14 BB15 BB16 quartno regno urbanorr weights, by(uid)
{txt}
 save in temp.dta

in Household file (with data only at the level of the household) 

{com}. merge uid using P:\GEORGIA\PREM\Poverty-update\1CP\data\all-data\temp.dta 
{txt}

Gap = fuladixa-fuladisa  

fuladixa= total monetary expenditures 
fuladisa= total monetary income 
Gap = not reported income 

{com}. table quartno  if BB1>=1 & BB1!=. [iw=weights], c(mean Gap)

give the average gap per household that has at least a member employed in sector 1

done for all sectors.


ADDING SELF-EMPLOYED 

IN Individual file (with data on occupation of each member of the household)

mark the hh that have a member in each sector. 

{com}. gen C3=B3
b3 indicates the workers' wage employment sector 

{com}. replace C3=c3_old if B3==. 
c3_old indicates self-employed sector

{com}. replace C3=c3_old if weinctot<seinctot & weinctot!=. & seinctot!=. & c3_old!=. 
self-employed classification is preferred to wage classification
if all wage income are lower than self-employed income 
(self-income from agr. are excluded)


unclassified with c3_old:
{com}. replace C3=1 if C3==. & c3==1
{com}. replace C3=1 if C3==. & c3==10
{com}. replace C3=12 if C3==. & c3==8322
{com}. replace C3=12 if C3==. & c3==8324
{com}. replace C3=14 if C3==. & c3==9414

mark the hh that have a member in each sector. 

{com}. egen CC1=sum (C3==1), by(uid)
{txt}
{com}. egen CC2=sum (C3==2), by(uid)
{txt}
{com}. egen CC3=sum (C3==3), by(uid)
{txt}
{com}. egen CC4=sum (C3==4), by(uid)
{txt}
{com}. egen CC5=sum (C3==5), by(uid)
{txt}
{com}. egen CC6=sum (C3==6), by(uid)
{txt}
{com}. egen CC7=sum (C3==7), by(uid)
{txt}
{com}. egen CC8=sum (C3==8), by(uid)
{txt}
{com}. egen CC9=sum (C3==9), by(uid)
{txt}
{com}. egen CC10=sum (C3==10), by(uid)
{txt}
{com}. egen CC11=sum (C3==11), by(uid)
{txt}
{com}. egen CC12=sum (C3==12), by(uid)
{txt}
{com}. egen CC13=sum (C3==13), by(uid)
{txt}
{com}. egen CC14=sum (C3==14), by(uid)
{txt}
{com}. egen CC15=sum (C3==15), by(uid)
{txt}
{com}. egen CC16=sum (C3==16), by(uid)
{txt}

{com}. table quartno C3 [iw=weights]
number of workers in each sectors

                              C3                                 
        sectors          1         2         3         4         5         6         7
 quarter
          1 {c |}  {res}1018134  18,820.5  80,358.6  42,475.3  37,525.6   202,750  5,659.93
     {txt}2 {c |}  {res}939,487  17,456.5  73,081.4  34,410.3  37,902.7   219,953  8,113.48
     {txt}3 {c |}  {res}962,993  23,198.8  63,651.3  21,959.9  34,900.7   253,533  9,316.93
     {txt}4 {c |}  {res}1060100  18,601.9  71,301.4  23,923.2  30,367.5   265,867  7,962.96
     {txt}5 {c |}  {res}1165193  17,904.1  70,114.8  23,207.2  34,680.5   249,466  6,373.83
     {txt}6 {c |}  {res}1171794  12,440.2    71,069    27,054  38,282.7   243,062  8,731.28
     {txt}7 {c |}  {res}1012848  9,357.78  70,244.2  25,984.7  41,468.3   232,570  8,830.91
     {txt}8 {c |}  {res}1148806  10,387.6  70,857.2  30,014.2  41,566.9   194,374  11,381.8
     {txt}9 {c |}  {res}1087701  8,934.08  91,785.7    28,055  39,696.9   191,319  14,067.2
    {txt}10 {c |}  {res}1056993  6,055.21  87,018.1  32,437.5  29,459.2   171,747  10,847.9
    {txt}11 {c |}  {res}981,475  12,222.4    84,350  32,393.4  26,217.6   168,550    10,361
    {txt}12 {c |}  {res}1068169  5,530.74  73,231.3  32,380.2  26,865.2   164,090  7,679.75
    {txt}13 {c |}  {res}1072425  4,746.55   123,361  22,157.2  34,413.9   172,499  17,382.5
    {txt}14 {c |}  {res}1242984  8,481.41   119,876  20,001.1  45,862.2   292,354  12,138.7
    {txt}15 {c |}  {res}972,123  4,161.88   102,444  29,285.6  29,633.3   194,303  13,227.3
    {txt}16 {c |}  {res}1090018  5,040.01   114,488  27,973.8  32,400.4   189,685  14,926.3
    {txt}17 {c |}  {res}1126927  5,016.92   118,309    29,686  31,208.7   200,031    15,892
    {txt}18 {c |}  {res}1117014  5,174.63   107,014  29,938.2  36,510.5   206,654  18,344.9
                                  C3                                 
                         8         9        10        11        12        13        14

          1 {c |} {res}86,117.9  10,249.8  4,026.26  72,117.5   161,106    91,011    56,746
     {txt}2 {c |} {res}73,769.3  9,325.03  2,354.92  67,336.5   151,701   102,608    63,567
     {txt}3 {c |} {res}87,039.2  8,305.18   1,200.9  84,074.6   159,483  98,841.1  70,210.5
     {txt}4 {c |} {res}83,725.5  9,355.81  1,084.12  77,843.1   150,556  87,920.2  77,437.7
     {txt}5 {c |} {res}76,606.3  9,419.73  5,465.44  80,892.7   144,901  82,800.2  73,999.3
     {txt}6 {c |} {res}78,816.6  9,233.44  2,639.57  81,977.7   157,950  83,775.6  67,417.8
     {txt}7 {c |} {res}86,824.6  8,267.76  4,938.31  92,827.2   139,257  74,607.8  78,622.5
     {txt}8 {c |} {res}97,912.2  9,181.37  5,105.14  88,466.2   150,183  90,281.2  83,628.7
     {txt}9 {c |} {res}95,900.6    15,398   3,868.1  81,229.7   151,373  84,474.2  74,324.6
    {txt}10 {c |} {res}91,357.1  9,412.86  7,992.85  89,730.2   151,565  83,130.1  88,725.9
    {txt}11 {c |} {res}86,952.2  12,426.6  7,841.47  88,992.2   148,542  89,143.7    85,361
    {txt}12 {c |} {res}81,226.9  9,682.41   6,705.2  89,288.9   161,738  94,669.4  82,455.4
    {txt}13 {c |} {res}61,411.4  13,093.5    29,765   100,924   128,499  81,722.9  46,485.4
    {txt}14 {c |}   {res}74,905  14,044.5  32,534.9   104,473   127,001  90,857.3  47,728.5
    {txt}15 {c |} {res}68,118.6  15,647.9  31,635.9  93,456.5   137,951  92,624.9  47,116.3
    {txt}16 {c |}   {res}76,779  14,297.1  28,583.7   103,994   139,373  90,874.7  44,776.2
    {txt}17 {c |}   {res}71,118  13,759.5    26,177   109,635   124,914  88,388.7  43,026.4
    {txt}18 {c |} {res}78,653.6  10,409.3  22,196.1   112,170   146,347  68,013.5    49,836


                          C3        
                         15        16

          1 {c |} {res}17,067.2  3,969.83
     {txt}2 {c |} {res}22,382.4  6,697.02
     {txt}3 {c |} {res}20,065.5  3,819.41
     {txt}4 {c |} {res}24,074.3  3,415.93
     {txt}5 {c |} {res}12,046.5  163.1106
     {txt}6 {c |} {res}12,777.9  370.8919
     {txt}7 {c |} {res}16,099.4  354.1105
     {txt}8 {c |} {res}10,421.5  2,115.77
     {txt}9 {c |} {res}10,160.7  1,983.78
    {txt}10 {c |} {res}5,595.01  1,912.08
    {txt}11 {c |} {res}7,881.65  1,464.29
    {txt}12 {c |} {res}6,405.42  2,254.69
    {txt}13 {c |} {res}3,318.02  1,835.13
    {txt}14 {c |} {res}4,681.12   2,413.3
    {txt}15 {c |} {res}6,435.55  1,175.58
    {txt}16 {c |} {res}7,879.59  978.5728
    {txt}17 {c |} {res}14,548.8  338.0446
    {txt}18 {c |} {res}16,835.2          


. table quartno C3 
observation per sectors
                                     C3                                
                         1     2     3     4     5     6     7     8     9    10    11

             1 {c |} {res}2,609    35   159    73    72   378    13   175    20     9   126
        {txt}2 {c |} {res}2,527    35   155    67    87   475    15   162    24     5   139
        {txt}3 {c |} {res}2,585    49   136    46    78   579    20   195    20     3   176
        {txt}4 {c |} {res}3,015    41   156    55    68   638    14   196    23     3   167
        {txt}5 {c |} {res}3,241    39   162    51    78   588    17   178    23    13   189
        {txt}6 {c |} {res}3,363    29   158    62    81   569    21   182    22     8   185
        {txt}7 {c |} {res}2,854    23   155    55    76   532    19   187    18    11   211
        {txt}8 {c |} {res}3,103    27   162    60    76   423    21   202    22    11   203
        {txt}9 {c |} {res}3,034    21   190    58    80   409    29   199    38    10   186
       {txt}10 {c |} {res}3,098    18   197    77    68   391    24   208    25    16   211
       {txt}11 {c |} {res}2,867    29   201    72    61   401    25   193    27    15   205
       {txt}12 {c |} {res}3,095    13   182    77    66   395    16   185    23    16   198
       {txt}13 {c |} {res}3,159    12   296    53    76   406    37   146    34    61   221
       {txt}14 {c |} {res}3,740    18   286    48   112   664    26   173    32    68   238
       {txt}15 {c |} {res}2,723    10   220    64    64   437    29   160    32    64   204
       {txt}16 {c |} {res}3,060    11   243    69    68   430    33   182    34    62   230
       {txt}17 {c |} {res}3,081    12   234    61    65   424    31   156    31    58   232
       {txt}18 {c |} {res}3,087    13   211    65    70   418    35   157    24    50   243

                            C3              
                          12    13    14    15    16

             1 {c |}   {res}314   165   107    39     7
        {txt}2 {c |}   {res}334   203   132    65    15
        {txt}3 {c |}   {res}368   212   143    50    11
        {txt}4 {c |}   {res}369   191   155    65    10
        {txt}5 {c |}   {res}344   187   155    27     1
        {txt}6 {c |}   {res}373   184   131    25     2
        {txt}7 {c |}   {res}329   159   157    38     1
        {txt}8 {c |}   {res}346   194   167    23     4
        {txt}9 {c |}   {res}344   182   149    22     2
       {txt}10 {c |}   {res}357   181   175    17     4
       {txt}11 {c |}   {res}342   197   181    19     3
       {txt}12 {c |}   {res}369   212   179    22     4
       {txt}13 {c |}   {res}303   184   109     8     3
       {txt}14 {c |}   {res}300   199   111     9     4
       {txt}15 {c |}   {res}323   202   101    10     2
       {txt}16 {c |}   {res}323   188    99    15     3
       {txt}17 {c |}   {res}279   184    86    30     1
       {txt}18 {c |}   {res}312   147    99    36      


{com}. collapse CC1 CC2 CC3 CC4 CC5 CC6 CC7 CC8 CC9 CC10 CC11 CC12 CC13 CC14 CC15 CC16,  by (uid)
{txt}
{com}. sort uid
{txt}
{com}. save "P:\GEORGIA\PREM\Poverty-update\1CP\data\all-data\temp.dta", replace
{txt}file P:\GEORGIA\PREM\Poverty-update\1CP\data\all-data\temp.dta saved

{com}. use "P:\GEORGIA\PREM\Poverty-update\1CP\data\all-data\Sectors.dta", clear
{txt}
{com}. sort uid
{txt}


{com}. merge uid using P:\GEORGIA\PREM\Poverty-update\1CP\data\all-data\temp.dta

{com}. gen inc= shemdaq+ shemtvit
sum of hh wage and self-employed income (ecl agr.)



{r
{txt}
{com}. merge uid using P:\GEORGIA\PREM\Poverty-update\1CP\data\all-data\temp1.dta

{com}. table quartno  if C3>=1 & C3!=. [iw=weights], c(mean Gap mean inc mean  fuladisa mean  fuladixa)

Gap = fuladixa-fuladisa hh total monetary expenditures - total monetary income 
inc = wage and self-employed hh income (excl. agr)

quartno                       mean(Gap)       mean(inc)  mean(fuladisa)  mean(fuladixa)

             1 {c |}       {res}95.96822        57.88058        106.8566        202.8249
        {txt}2 {c |}       {res}73.75124        62.88428        119.3702        193.1215
        {txt}3 {c |}       {res}90.52765        60.23005        104.6174         195.145
        {txt}4 {c |}       {res}85.19223        65.87598        100.8459        186.0381
        {txt}5 {c |}       {res}75.81876        66.43712        104.8017        180.6205
        {txt}6 {c |}       {res}97.38256        74.38725        127.0831        224.4657
        {txt}7 {c |}       {res}70.46142        73.51774         125.517        195.9785
        {txt}8 {c |}       {res}53.89624        75.11636        139.7281        193.5891
        {txt}9 {c |}       {res}32.18465        75.99636        152.9631        185.1477
       {txt}10 {c |}       {res}62.35899        66.55569        130.0779        191.1946
       {txt}11 {c |}       {res}60.57525        68.92979        142.2739        203.1561
       {txt}12 {c |}       {res}67.62524        64.11164        131.8202        199.4454
       {txt}13 {c |}       {res}58.94275         67.4612        134.0936        193.0864
       {txt}14 {c |}       {res}83.99374         74.3076        148.9083        232.9021
       {txt}15 {c |}       {res}98.92775        68.92461        136.0107        234.9831
       {txt}16 {c |}       {res}72.89831        76.58041        149.1094        222.0077
       {txt}17 {c |}       {res}71.00029        79.04671        142.9139        213.9615
       {txt}18 {c |}       {res}88.74461        80.51549        159.9705        248.7152