I'm looking to transform a quarterly variable into a monthly variable so that it matches the other monthly variables I have. The way I want to do is by using the latest quarterly observation for the 2 months before as well. The quarterly variable I have is the expense ratio of a fund. The quarterly data looks as follows (Variables: Fund Identifier, Date, and Expense ratio):
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input double fundno long date double exp_ratio 105 20178 .023799999999999998 105 20269 .023799999999999998 105 20361 .023799999999999998 105 20453 .023799999999999998 105 20544 .024399999999999998 105 20635 .024399999999999998 105 20727 .024399999999999998 105 20818 .024399999999999998 105 20909 .024 105 21000 .024 105 21091 .024 105 21182 .024 105 21272 .0245 105 21364 .0245 105 21455 .0245 105 21549 .0245 105 21637 .0246 105 21728 .0246 105 21822 .0246 105 21914 .0246 2704 20178 .0027 2704 20269 .0027 2704 20361 .0027 2704 20453 .0027 2704 20544 .0039000000000000003 2704 20635 .0039000000000000003 2704 20727 .0039000000000000003 2704 20818 .0039000000000000003 2704 20909 .0042 2704 21000 .0042 2704 21091 .0042 2704 21182 .0042 2704 21272 .004 2704 21364 .004 2704 21455 .004 2704 21549 .004 2704 21637 .0039000000000000003 2704 21728 .0039000000000000003 2704 21822 .0039000000000000003 2704 21914 .0039000000000000003 2706 20178 .0028000000000000004 2706 20269 .0028000000000000004 2706 20361 .0028000000000000004 2706 20453 .0028000000000000004 2706 20544 .0037 2706 20635 .0037 2706 20727 .0037 2706 20818 .0037 2706 20909 .0037 2706 21000 .0037 2706 21091 .0037 2706 21182 .0037 2706 21272 .0038 2706 21364 .0038 2706 21455 .0038 2706 21549 .0038 2706 21637 .0038 2706 21728 .0038 2706 21822 .0038 2706 21914 .0038 2708 20178 .0029 2708 20269 .0029 2708 20361 .0029 2708 20453 .0029 2708 20544 .0038 2708 20635 .0038 2708 20727 .0038 2708 20818 .0038 2708 20909 .0037 2708 21000 .0037 2708 21091 .0037 2708 21182 .0037 2708 21272 .0038 2708 21364 .0038 2708 21455 .0038 2708 21549 .0038 2708 21637 .0038 2708 21728 .0038 2708 21822 .0038 2708 21914 .0038 2711 20178 . 2711 20269 . 2711 20361 . 2711 20453 . 2724 20178 .0029 2724 20269 .0029 2724 20361 .0029 2724 20453 .0029 2724 20544 .0039000000000000003 2724 20635 .0039000000000000003 2724 20727 .0039000000000000003 2724 20818 .0039000000000000003 2724 20909 .0038 2724 21000 .0038 2724 21091 .0038 2724 21182 .0038 2724 21272 .0038 2724 21364 .0038 2724 21455 .0038 2724 21549 .0038 end format %td date
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input int(fundno date) double exp_ratio 20118 105 .0238 20146 105 .0238 20178 105 .0238 20208 105 .0238 20237 105 .0238 20269 105 .0238 20300 105 .0238 20331 105 .0238 20361 105 .0238 20391 105 .0238 20422 105 .0238 20453 105 .0238 20482 105 .024399999999999998 20513 105 .024399999999999998 20544 105 .024399999999999998 20573 105 .024399999999999998 20605 105 .024399999999999998 20635 105 .024399999999999998 20664 105 .024399999999999998 20697 105 .024399999999999998 20727 105 .024399999999999998 20758 105 .024399999999999998 20788 105 .024399999999999998 20818 105 .024399999999999998 20850 105 .024 20878 105 .024 20909 105 .024 20937 105 .024 20970 105 .024 21000 105 .024 21031 105 .024 21062 105 .024 21091 105 .024 21123 105 .024 21153 105 .024 21182 105 .024 21215 105 .0245 21243 105 .0245 21272 105 .0245 21304 105 .0245 21335 105 .0245 21364 105 .0245 21396 105 .0245 21427 105 .0245 21455 105 .0245 21488 105 .0245 21518 105 .0245 21549 105 .0245 21580 105 .0246 21608 105 .0246 21637 105 .0246 21669 105 .0246 21700 105 .0246 21728 105 .0246 21761 105 .0246 21791 105 .0246 21822 105 .0246 21853 105 .0246 21882 105 .0246 21914 105 .0246 20178 2704 .0027 20208 2704 .0027 20269 2704 .0027 20300 2704 .0027 20331 2704 .0027 20361 2704 .0027 20422 2704 .0027 20453 2704 .0027 20513 2704 .0037 20544 2704 .0037 20605 2704 .0037 20635 2704 .0037 20697 2704 .0037 20727 2704 .0037 20758 2704 .0037 20788 2704 .0037 20850 2704 .0037 20878 2704 .0037 20909 2704 .0037 20970 2704 .0037 21000 2704 .0037 21031 2704 .0037 21062 2704 .0037 21123 2704 .0037 21153 2704 .0037 end format %tdnn/dd/CCYY fundno
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input int(date fundno) double(TNA Returnshare NAVShare ExcessReturn) 20118 105 34.8 -.021489 12.75 -.021489 20146 105 37 .061176 13.53 .061176 20178 105 37.3 .005174 13.6 .005174 20208 105 38 .004412 13.66 .004412 20237 105 38.4 .010981 13.81 .010981 20269 105 37.6 -.009413 13.68 -.009413 20300 105 34.7 -.002193 13.65 -.002193 20331 105 32.8 -.069597 12.7 -.069597 20361 105 31.8 -.040157 12.19 -.040157 20391 105 33.7 .063167 12.96 .063167 20422 105 33.6 .004403 12.63 .004403 20453 105 30.1 -.029295 12.26 -.029395 20482 105 28.2 -.062806 11.49 -.062906 20513 105 28 .001741 11.51 .0015409999999999998 20544 105 29.5 .069505 12.31 .06930499999999999 20573 105 29 .006499 12.39 .006398999999999999 20605 105 29.1 .008071 12.49 .007971 20635 105 28.2 -.010408 12.36 -.010608000000000001 20664 105 28.9 .05178 13 .05158 20697 105 28.4 .002308 13.03 .002108 20727 105 28.1 .003837 13.08 .003637 20758 105 26.8 -.027523 12.72 -.027722999999999998 20788 105 26.5 .011792 12.87 .011692000000000001 20818 105 26.5 .004555 12.79 .004255 20850 105 26.9 .028147 13.15 .027746999999999997 20878 105 27.3 .026616 13.5 .026216 20909 105 27.6 .017778 13.74 .017477999999999997 20937 105 28.2 .024745 14.08 .024245 20970 105 28.6 .020597 14.37 .019997 21000 105 28 .004871 14.44 .0042710000000000005 21031 105 28.3 .01662 14.68 .01592 21062 105 28.5 .01158 14.85 .01068 21091 105 29 .027609 15.26 .026709 21123 105 29.4 .027523 15.68 .026622999999999997 21153 105 30 .019133 15.98 .018333000000000002 21182 105 29.6 .010415 14.96 .009515000000000001 21215 105 30.9 .042781 15.6 .041681 21243 105 29.1 -.041026 14.96 -.042126 21272 105 28.9 -.005348 14.88 -.006548 21304 105 28.3 -.012097 14.7 -.013497 21335 105 28.6 .015646 14.93 .014246 21364 105 27 -.006028 14.84 -.007428000000000001 21396 105 27 .012129 15.02 .010528999999999998 21427 105 27.8 .030626 15.48 .029026 21455 105 27.3 -.014212 15.26 -.015712 21488 105 24.3 -.102228 13.7 -.104128 21518 105 23.7 .016058 13.92 .014258 21549 105 21.2 -.080647 10.32 -.082547 21580 105 23.1 .081395 11.16 .07929499999999999 21608 105 24.5 .042115 11.63 .040315 21637 105 24.3 .010318 11.75 .008418 21669 105 25.1 .046809 12.3 .044709000000000006 21700 105 23.6 -.04878 11.7 -.050879999999999995 21728 105 25 .063248 12.44 .061447999999999996 21761 105 25.1 .009646 12.56 .007746 21791 105 24.3 -.023089 12.27 -.024689 21822 105 24.3 .0163 12.47 .014499999999999999 21853 105 25.6 .025662 12.79 .024162 21882 105 26.1 .027365 13.14 .026165 21914 105 26.6 .024275 12.28 .022875000000000003 20118 2704 309.9 .002588 11.62 .002588 20146 2704 312.1 .013769 11.78 .013769 20178 2704 310.8 -.003396 11.74 -.003396 20208 2704 311.6 .00937 11.85 .00937 20237 2704 309.8 -.001688 11.83 -.001688 20269 2704 306.6 -.010989 11.7 -.010989 20300 2704 305.1 -.002564 11.67 -.002564 20331 2704 299.4 -.019709 11.44 -.019709 20361 2704 296.5 -.011364 11.31 -.011364 20391 2704 300.7 .02122 11.55 .02122 20422 2704 298.8 -.004329 11.5 -.004329 20453 2704 294.6 -.009258 10.62 -.009358 20482 2704 289.7 -.013183 10.48 -.013283 20513 2704 288.9 0 10.48 -.0002 20544 2704 295 .026718 10.76 .026518 20573 2704 297.9 .010223 10.87 .010123 20605 2704 299.6 .00184 10.89 .00174 20635 2704 300.4 .004591 10.94 .004391 20664 2704 304.8 .014625 11.1 .014425 20697 2704 304.8 .002703 11.13 .002503 20727 2704 308.9 .003594 11.17 .003394 20758 2704 308.5 -.007162 11.09 -.007362 20788 2704 305.5 -.001803 11.07 -.001903 20818 2704 306.2 .005548 10.88 .005248 20850 2704 306.5 .009191 10.98 .008791 20878 2704 404.6 .009107 11.08 .008707000000000001 20909 2704 405.4 .002708 11.11 .002408 20937 2704 410.8 .006301 11.18 .005801000000000001 20970 2704 412.5 .007156 11.26 .006556 21000 2704 412 .001776 11.28 .001176 21031 2704 416.1 .009752 11.39 .009052000000000001 21062 2704 417.5 .003512 11.43 .0026119999999999997 21091 2704 420 .005249 11.49 .0043490000000000004 21123 2704 423.6 .005222 11.55 .004322 21153 2704 426.8 .005195 11.61 .004395 21182 2704 427.3 .003736 11.47 .002836 21215 2704 433.4 .013078 11.62 .011977999999999999 21243 2704 428.8 -.01463 11.45 -.01573 21272 2704 429.4 -.001747 11.43 -.002947 21304 2704 430 .000875 11.44 -.000525 end format %tdnn/dd/CCYY date
Best,
Tom
0 Response to Transforming a quarterly variable into a monthly variable and merging two databases
Post a Comment