-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- name: log: /disk/nber10/SCCS/sipp/2004/wealth2001t3.log log type: text opened on: 8 Mar 2011, 10:18:26 . ** by Jean Roth, jroth@nber.org , 2009-08-03 . set more off . set mem 500m (512000k) . use /homes/data/sipp/2001/sipp01t3 . ********************************************************************** . ** Checking that sample unit and person ids are unique identifiers . duplicates report ssuid epppnum Duplicates in terms of ssuid epppnum -------------------------------------- copies | observations surplus ----------+--------------------------- 1 | 71280 0 -------------------------------------- . assert `r(unique_value)'==`r(N)' . ** Note that a sample unit may contain multiple households identified by shhadid . ********************************************************************** . ** Total Wealth . describe thhtwlth thhtheq thhvehcl thhbeq thhintbk thhintot rhhstk thhore thhotast thhira thhthrif storage display value variable name type format label variable label -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- thhtwlth double %23.0g thhtwlth RE: Total Wealth recode thhtheq double %23.0g thhtheq RE: Home Equity recode thhvehcl double %23.0g thhvehcl RE: Net equity in vehicles thhbeq double %23.0g thhbeq RE: Business Equity thhintbk double %23.0g thhintbk RE: Interest Earning assets held in banking thhintot double %23.0g thhintot RE: Interest Earning assets held in other rhhstk double %23.0g rhhstk RE: Equity in stocks and mutual fund shares thhore double %23.0g thhore RE: Equity in real estate that is not your thhotast double %23.0g thhotast RE: Equity in other assets thhira double %23.0g thhira RE: Equity in IRA and KEOGH accounts thhthrif double %23.0g thhthrif RE: Equity in 401K and Thrift savings . ** wealth needs to be a double to preserve precision . gen double wealth = thhtheq+thhvehcl+thhbeq+thhintbk+thhintot+rhhstk +thhore+thhotast+thhira+thhthrif . assert thhtwlth == wealth . ********************************************************************** . ** Home Equity . describe thhtheq tpropval tmhval thhmortg storage display value variable name type format label variable label -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- thhtheq double %23.0g thhtheq RE: Home Equity recode tpropval long %23.0g tpropval RE: Current value of property tmhval long %23.0g tmhval RE: Amount mobile would sell for thhmortg double %23.0g thhmortg RE: Total Debt owed on Home . gen home = (tpropval+tmhval)-thhmortg . assert thhtheq == home . ********************************************************************** . ** Vehicle Equity . describe thhvehcl tcarval1 ta1amt tcarval2 ta2amt tcarval3 ta3amt tov1val tov1amt tov2val tov2amt storage display value variable name type format label variable label -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- thhvehcl double %23.0g thhvehcl RE: Net equity in vehicles tcarval1 long %23.0g tcarvalm RE: Car value for first vehicle ta1amt long %23.0g ta1amt RE: Amount owed for 1st vehicle tcarval2 long %23.0g tcarvale RE: Car value for second vehicle ta2amt long %23.0g ta2amt RE: Amount owed for second vehicle tcarval3 long %23.0g tcarvalk RE: Car value for third vehicle ta3amt long %23.0g ta3amt RE: Amount owed for third vehicle tov1val long %23.0g tov1val RE: 1st other vehicle value tov1amt long %23.0g tov1amt RE: Amount owed for first other vehicle tov2val long %23.0g tov2val RE: Second other vehicle value tov2amt long %23.0g tov2amt RE: Amount owed for 2nd other vehicle . gen vehicle = (tcarval1-ta1amt)+(tcarval2-ta2amt)+(tcarval3-ta3amt)+(tov1val-tov1amt)+(tov2val-tov2amt) . assert thhvehcl == vehicle . ********************************************************************** . ** Business Equity . describe thhbeq evbow1 tvbva1 tvbde1 evbow2 tvbva2 tvbde2 storage display value variable name type format label variable label -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- thhbeq double %23.0g thhbeq RE: Business Equity evbow1 int %15.0g evbow1l BU: Percent of Business owned for first tvbva1 long %23.0g tvbva1l BU: The value of the business for the first tvbde1 long %23.0g tvbde1l BU: The total debt owed against the first evbow2 int %15.0g evbow2l BU: Percent of Business owned for second tvbva2 long %23.0g tvbva2l BU: The value of the business for business tvbde2 long %23.0g tvbde2l BU: The total debt owed against the second . ** The value of all the person's businesses in the household is applied to the household . ** So, calculating person's business equity first, then applying the sum of all person's to the household . gen p_business = (evbow1/100*(tvbva1-tvbde1))+(evbow2/100*(tvbva2-tvbde2)) . bysort ssuid shhadid: egen business=sum(p_business) . ** Truncating to the integer value works a bit better than rounding . replace business=int(business) (65 real changes made) . ** Checking that calculated household business equity is within $1 of Census' . assert abs(thhbeq-business)<=1 . ** Generating a count of households with people who own one business . egen bus1=count(thhbeq) if evbow1 !=0 | tvbva1 !=0 | tvbde1 !=0 , by(ssuid shhadid) (66829 missing values generated) . ** Placing records with business owner flag at top of household records . sort ssuid shhadid bus1 . ** Applying the business owner in the household flag to all household records . by ssuid shhadid: replace bus1 = bus1[1] (7472 real changes made) . replace bus1 = 0 if bus1 == . (59357 real changes made) . ** Generating a count of households with people who own a second businesses . egen bus2=count(thhbeq) if evbow2 !=0 | tvbva2 !=0 | tvbde2 !=0 , by(ssuid shhadid) (70895 missing values generated) . ** Placing records with business owner flag at top of household records . sort ssuid shhadid bus2 . ** Applying the business owner in the household flag to all household records . by ssuid shhadid: replace bus2 = bus2[1] (735 real changes made) . replace bus2 = 0 if bus2 == . (70160 real changes made) . gen p_business1 = (evbow1/100*(tvbva1-tvbde1)) if bus2==0 (1120 missing values generated) . bysort ssuid shhadid: egen business1=sum(p_business1) . ** Truncating to the integer value works a bit better than rounding . replace business1=int(business1) (45 real changes made) . ** Checking that calculated household business equity is within $1 of Census' . assert abs(thhbeq-business1)<=1 if bus2==0 . *list ssuid shhadid epppnum bus1 thhbeq evbow1 tvbva1 tvbde1 if bus1>1 & bus1<. . . *list thhbeq evbow1 tvbva1 tvbde1 bus1 ssuid shhadid if ssuid=="019133717494" . list thhbeq evbow2 tvbva2 tvbde2 bus2 ssuid shhadid if ssuid=="104418451695" . ********************************************************************** . ** Bank Interest . ** Again, the sum of each person's wealth is applied to the household . describe thhintbk tiajta tiaita storage display value variable name type format label variable label -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- thhintbk double %23.0g thhintbk RE: Interest Earning assets held in banking tiajta long %23.0g tiajta IE: Amount in joint interest earning account tiaita long %23.0g tiaita IE: Amount in own interest earning account . gen p_bank_int = tiajta+tiaita . bysort ssuid shhadid: egen bank_int=sum(p_bank_int) . assert thhintbk == bank_int . ********************************************************************** . ** Other Interest . ** The sum of each person's wealth is applied to the household . describe thhintot timja timia storage display value variable name type format label variable label -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- thhintot double %23.0g thhintot RE: Interest Earning assets held in other timja long %23.0g timja IE: Amount in joint bonds/US securities timia long %23.0g timia IE: Amount of bonds/securities in own name . gen p_oth_int = timja+timia . bysort ssuid shhadid: egen oth_int=sum(p_oth_int) . assert thhintot == oth_int . ********************************************************************** . ** Stocks . ** The sum of each person's wealth is applied to the household . describe rhhstk esmjv esmjmav esmiv esmimav storage display value variable name type format label variable label -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- rhhstk double %23.0g rhhstk RE: Equity in stocks and mutual fund shares esmjv long %23.0g esmjv SM: Value of joint stocks/funds owned with esmjmav long %23.0g esmjmav SM: Amount of debt on jointly owned esmiv long %23.0g esmiv SM: Value of stocks/funds in own name esmimav long %23.0g esmimav SM: Debt on stocks/funds in own name . gen p_stock = (esmjv-esmjmav)+(esmiv-esmimav) . bysort ssuid shhadid: egen stock=sum(p_stock) . assert rhhstk == stock . ** 1993t4 does not ask tm8040, tm8050 about "amount of the debt or margin against account" . gen p_stock_no_debt = (esmjv)+(esmiv) . bysort ssuid shhadid: egen stock_no_debt=sum(p_stock_no_debt) . ********************************************************************** . ** Other real estate - Not home . ** The sum of each person's wealth is applied to the household . ** tothreva: All persons in HH get the reference person's response duplicated to their record . ** trtsha: real estate jointly owned with other than spouse . ** trjmv: joint, non-residence ASKED OF ONLY ONE SPOUSE. RESPONSE IS DIVIDED BY 2, AND THE DIVIDED AMOUNT IS COPIED TO BOTH SPOUSES RECORDS. . ** trimv: owned in own name . describe thhore tothreva trtsha trjmv trjpri trimv tripri storage display value variable name type format label variable label -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- thhore double %23.0g thhore RE: Equity in real estate that is not your tothreva long %23.0g tothreva RE: Equity in other real estate trtsha long %23.0g trtsha RT: Share of rental property held with other trjmv long %23.0g trjmv RT: Market value of joint rent not on land trjpri long %23.0g trjpri RT: Principal owed on joint rental property trimv long %23.0g trimv RT: Market value of rental property owned in tripri long %23.0g tripri RT: Principal owed on rental property in own . ** trtsha, trimv, and trjmv apply to the whole hh, so hh-wise variables that are cum sums are needed . bysort ssuid shhadid: egen h_trt=sum(trtsha) . bysort ssuid shhadid: egen h_trj=sum(trjmv-trjpri) . bysort ssuid shhadid: egen h_tri=sum(trimv-tripri) . gen oreal = tothreva+h_trt+h_trj+h_tri . sort ssuid shhadid epppnum . *list ssuid shhadid epppnum thhore *real* tothreva trtsha trjmv trjpri trimv tripri if ssuid=="066925123035" , nol . assert thhore == oreal . ********************************************************************** . ** Other assets . ** The sum of each person's wealth is applied to the household . describe thhotast tmjp tmip eoaeq ealowa talsbv taljcha talicha storage display value variable name type format label variable label -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- thhotast double %23.0g thhotast RE: Equity in other assets tmjp long %23.0g tmjp M0: Principal owed on joint mortgage(s) held tmip long %23.0g tmip M0: Principal owed on mortgage(s) in own eoaeq long %23.0g eoaeq OA: Equity in investments ealowa long %15.0g ealowa AL: Amount owed to you for sale talsbv long %15.0g talsbv AL: Face Value of U.S. Savings Bonds taljcha int %23.0g taljcha AL: Estimate of a joint non-interest talicha int %23.0g talicha AL: Estimate of own non-interest checking . gen p_oth_ass = tmjp+tmip+eoaeq+ealowa+talsbv+taljcha+talicha . bysort ssuid shhadid: egen oth_ass=sum(p_oth_ass) . *assert thhotast == oth_ass . ** NOTE: A handful of records do not match the formula . count if thhotast 32737 . count if thhotast !=oth_ass 19 . gen diff_ass = thhotast - oth_ass if thhotast !=oth_ass (71261 missing values generated) . sum diff_ass Variable | Obs Mean Std. Dev. Min Max -------------+-------------------------------------------------------- diff_ass | 19 668421.1 1675133 50000 5420000 . di "TMIP topcode==200,000 ; TMJP topcode==290,000 TMIP topcode==200,000 ; TMJP topcode==290,000 . count if tmjp==290000 10 . count if tmjp==290000 & diff_ass==0 0 . count if tmip==200000 9 . count if tmip==200000 & diff_ass==0 0 . list ssuid shhadid epppnum thhotast o*ass p*ass tmjp tmip eoaeq ealowa talsbv taljcha talicha if thhotast!=oth_ass , nol +----------------------------------------------------------------------------------------------------------------------------------+ | ssuid shhadid epppnum thhotast oth_ass p_oth_~s tmjp tmip eoaeq ealowa talsbv taljcha talicha | |----------------------------------------------------------------------------------------------------------------------------------| 14764. | 315344796125 11 0101 710000 590000 295000 290000 0 0 0 0 5000 0 | 14765. | 315344796125 11 0102 710000 590000 295000 290000 0 0 0 0 5000 0 | 15260. | 315688986318 11 0101 250000 200000 200000 0 200000 0 0 0 0 0 | 15261. | 315688986318 11 0102 250000 200000 0 0 0 0 0 0 0 0 | 23702. | 458344392159 11 0101 6000000 580000 290000 290000 0 0 0 0 0 0 | |----------------------------------------------------------------------------------------------------------------------------------| 23703. | 458344392159 11 0102 6000000 580000 290000 290000 0 0 0 0 0 0 | 31258. | 514600754873 11 0101 380000 200000 200000 0 200000 0 0 0 0 0 | 31259. | 514600754873 11 0102 380000 200000 0 0 0 0 0 0 0 0 | 31260. | 514600754873 11 0301 380000 200000 0 0 0 0 0 0 0 0 | 32093. | 557228077552 11 0101 257000 207000 0 0 0 0 0 0 0 0 | |----------------------------------------------------------------------------------------------------------------------------------| 32094. | 557228077552 11 0102 257000 207000 207000 0 200000 0 0 7000 0 0 | 32983. | 557552229510 11 0101 700300 580300 290000 290000 0 0 0 0 0 0 | 32984. | 557552229510 11 0102 700300 580300 300 0 0 0 0 0 0 300 | 32985. | 557552229510 11 0103 700300 580300 290000 290000 0 0 0 0 0 0 | 35599. | 563860881363 21 0101 707600 587600 291000 290000 0 0 0 1000 0 0 | |----------------------------------------------------------------------------------------------------------------------------------| 35600. | 563860881363 21 0102 707600 587600 296600 290000 0 0 0 6600 0 0 | 47298. | 685925944668 11 0101 250000 200000 200000 0 200000 0 0 0 0 0 | 47299. | 685925944668 11 0102 250000 200000 0 0 0 0 0 0 0 0 | 63065. | 874925202653 11 0101 380000 200000 200000 0 200000 0 0 0 0 0 | +----------------------------------------------------------------------------------------------------------------------------------+ . *list ssuid shhadid epppnum thhotast *ass tmjp tmip eoaeq ealowa talsbv taljcha talicha if ssuid=="077344437402" , nol . *list ssuid shhadid epppnum thhotast *ass tmjp tmip eoaeq ealowa talsbv taljcha talicha if ssuid=="104344074647" , nol . ** 1993t4 had only four other asset variables, I think . gen p_oth_ass4 = eoaeq+talsbv+taljcha+talicha . bysort ssuid shhadid: egen oth_ass4=sum(p_oth_ass4) . sum thhotast tmjp tmip eoaeq ealowa talsbv taljcha talicha Variable | Obs Mean Std. Dev. Min Max -------------+-------------------------------------------------------- thhotast | 71280 4724.502 100548.2 0 1.81e+07 tmjp | 71280 154.3818 4521.254 0 290000 tmip | 71280 137.8786 3790.801 0 200000 eoaeq | 71280 1045.509 73029.4 0 1.81e+07 ealowa | 71280 187.1925 8829.856 0 1200000 -------------+-------------------------------------------------------- talsbv | 71280 195.9186 1751.504 0 30000 taljcha | 71280 90.95603 443.711 0 5000 talicha | 71280 96.99827 522.6362 0 6000 . ********************************************************************** . ** IRA and KEOGH . ** The sum of each person's wealth is applied to the household . describe thhira talrb talkb storage display value variable name type format label variable label -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- thhira double %23.0g thhira RE: Equity in IRA and KEOGH accounts talrb long %23.0g talrb AL: Market value of IRA account(s) in own talkb long %23.0g talkb AL: Market value of KEOGH account(s) . gen p_ira = talrb+talkb . bysort ssuid shhadid: egen ira=sum(p_ira) . assert thhira == ira . ********************************************************************** . ** 401K and Thrift . ** The sum of each person's wealth is applied to the household . describe thhthrif taltb storage display value variable name type format label variable label -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- thhthrif double %23.0g thhthrif RE: Equity in 401K and Thrift savings taltb long %23.0g taltb AL: Market value of 401K in own name . bysort ssuid shhadid: egen thrift=sum(taltb) . assert thhthrif == thrift . ********************************************************************** . gen fileid = "2001t3" . sort ssuid shhadid epppnum . saveold ~/bulk/sipp/sipp01t3, replace file ~/bulk/sipp/sipp01t3.dta saved . exit,clear end of do-file . exit,clear