---------------------------------------------------------------------------------------------------------------------------------- name: log: C:\Users\rwr\Dropbox\basu-house\analysis\code\../temp/copy_excel.log log type: text opened on: 10 Feb 2017, 14:41:09 . /***********************************************************************************/ > /*** DATA ***/ > /***********************************************************************************/ > import excel using `myoutput'user_cost_var_data.xlsx, firstrow; . gen dateq = yq(year,quarter); . tsset dateq, quarterly; time variable: dateq, 1948q1 to 2016q1 delta: 1 quarter . merge 1:1 dateq using `myinput'fred4ucvar_data, keepusing(dateq lmacrowage_ipd); Result # of obs. ----------------------------------------- not matched 5 from master 0 (_merge==1) from using 5 (_merge==2) matched 273 (_merge==3) ----------------------------------------- . merge 1:1 dateq using `myinput'lentry_quarterly_ipd, keep(match) nogen; (note: variable year was int, now float to accommodate using data's values) (note: variable quarter was byte, now float to accommodate using data's values) Result # of obs. ----------------------------------------- not matched 0 matched 277 ----------------------------------------- . merge 1:1 dateq using `myinput'lentry_quarterly_ipd_cumtight, keep(match) nogen; Result # of obs. ----------------------------------------- not matched 0 matched 277 ----------------------------------------- . drop if dateq < yq(1965,1); (72 observations deleted) . local myvars lentry_wage_ipd lentry_wage_ipd_cumtight luc_ipd luc_ipd_cumtight lmacrowage_ipd; . sort dateq; . gen trend = _n; . foreach ii of loc myvars {; 2. rename `ii' temp; 3. reg temp trend; 4. predict `ii', residuals; 5. drop temp; 6. }; Source | SS df MS Number of obs = 205 -------------+---------------------------------- F(1, 203) = 12057.65 Model | 141.319274 1 141.319274 Prob > F = 0.0000 Residual | 2.37922017 203 .011720296 R-squared = 0.9834 -------------+---------------------------------- Adj R-squared = 0.9834 Total | 143.698494 204 .704404381 Root MSE = .10826 ------------------------------------------------------------------------------ temp | Coef. Std. Err. t P>|t| [95% Conf. Interval] -------------+---------------------------------------------------------------- trend | .0140303 .0001278 109.81 0.000 .0137783 .0142822 _cons | 1.177168 .015178 77.56 0.000 1.147241 1.207094 ------------------------------------------------------------------------------ Source | SS df MS Number of obs = 205 -------------+---------------------------------- F(1, 203) = 14563.04 Model | 149.705272 1 149.705272 Prob > F = 0.0000 Residual | 2.08680093 203 .010279808 R-squared = 0.9863 -------------+---------------------------------- Adj R-squared = 0.9862 Total | 151.792073 204 .744078789 Root MSE = .10139 ------------------------------------------------------------------------------ temp | Coef. Std. Err. t P>|t| [95% Conf. Interval] -------------+---------------------------------------------------------------- trend | .0144405 .0001197 120.68 0.000 .0142046 .0146765 _cons | 1.109566 .0142147 78.06 0.000 1.081539 1.137593 ------------------------------------------------------------------------------ Source | SS df MS Number of obs = 205 -------------+---------------------------------- F(1, 203) = 10798.71 Model | 115.235375 1 115.235375 Prob > F = 0.0000 Residual | 2.16625609 203 .010671212 R-squared = 0.9815 -------------+---------------------------------- Adj R-squared = 0.9815 Total | 117.401631 204 .575498191 Root MSE = .1033 ------------------------------------------------------------------------------ temp | Coef. Std. Err. t P>|t| [95% Conf. Interval] -------------+---------------------------------------------------------------- trend | .0126695 .0001219 103.92 0.000 .0124291 .0129098 _cons | 1.171007 .0144827 80.86 0.000 1.142451 1.199563 ------------------------------------------------------------------------------ Source | SS df MS Number of obs = 205 -------------+---------------------------------- F(1, 203) = 11788.14 Model | 125.018702 1 125.018702 Prob > F = 0.0000 Residual | 2.15290855 203 .010605461 R-squared = 0.9831 -------------+---------------------------------- Adj R-squared = 0.9830 Total | 127.171611 204 .62339025 Root MSE = .10298 ------------------------------------------------------------------------------ temp | Coef. Std. Err. t P>|t| [95% Conf. Interval] -------------+---------------------------------------------------------------- trend | .0131963 .0001215 108.57 0.000 .0129567 .013436 _cons | 1.060801 .0144381 73.47 0.000 1.032333 1.089269 ------------------------------------------------------------------------------ Source | SS df MS Number of obs = 205 -------------+---------------------------------- F(1, 203) = 15312.78 Model | 13.2961725 1 13.2961725 Prob > F = 0.0000 Residual | .176266038 203 .000868306 R-squared = 0.9869 -------------+---------------------------------- Adj R-squared = 0.9869 Total | 13.4724385 204 .066041365 Root MSE = .02947 ------------------------------------------------------------------------------ temp | Coef. Std. Err. t P>|t| [95% Conf. Interval] -------------+---------------------------------------------------------------- trend | .0043036 .0000348 123.74 0.000 .004235 .0043721 _cons | 3.820701 .0041312 924.83 0.000 3.812555 3.828847 ------------------------------------------------------------------------------ . tsline lentry_wage_ipd luc_ipd lmacrowage_ipd, > lc(black red blue) lw(0.8 0.8 0.8) > xtitle("") ytitle("Detrended Wages") ylabel(,angle(h)) > legend(region(lstyle(none)) order(2 1 3) label(1 "New Hire Wage") label(2 "User Cost of Labor") label(3 "Average Hourly Earnings > : BLS")); . export excel year quarter > lentry_wage_ipd lentry_wage_ipd_cumtight > luc_ipd luc_ipd_cumtight > lmacrowage_ipd using `final_dir'wages.xlsx, firstrow(variables) replace; file ../../wages.xlsx saved . end of do-file . exit, clear