Merging Quarterly data with Monthly

[Nour Mohamed]

Dear StataList

I'm trying to merge a master dataset with monthly data with a quarterly source dataset


Master:

 dataex

----------------------- copy starting from the next line -----------------------


	
	
* Example generated by -dataex-. For more info, type help dataex
clear
input str32 Destination float Mdate double Industrial_FDI float VIX str3 country_code float(GPR GPRC)
"Australia" 518 153.51691017050715 29.15 "AUS"  358.7112   .4018736
"Australia" 521  92.45285795909041 19.52 "AUS" 145.76973  .14463495
"Australia" 524  316.7221999533908 22.72 "AUS" 118.19628  .13827597
"Australia" 527   5.05197216363338 18.31 "AUS" 129.46507  .09875128
"Australia" 530  172.2434735838447 16.74 "AUS" 138.53207  .13726443
"Australia" 533   72.9297079165198 14.34 "AUS" 137.23392   .0835771
"Australia" 536   190.863280678025 13.34 "AUS"  153.8716   .1525439
"Australia" 539     95.34706331045 13.29 "AUS" 109.89523  .10479184
"Australia" 542   99.6998794352905 14.02 "AUS"  90.41048 .062997535
"Australia" 545   72.5522678629729 12.04 "AUS"  81.12405  .08733098
"Australia" 548  97.42602747973937 11.92 "AUS" 103.80766  .07302041
"Australia" 554  270.6699214447391 11.39 "AUS"  92.79762  .09469154
"Australia" 563   5.27704485488127 11.56 "AUS"  90.60656 .071516804
"Australia" 566   7.66257995735608 14.64 "AUS"   87.3365  .06939224
"Australia" 575  174.6726728318188  22.5 "AUS"  94.96749  .07453255
"Australia" 581   1.18229955687413 23.95 "AUS"  87.54842   .0782881
"Australia" 590   13.0529831201393 44.14 "AUS"  72.64462  .04510443
"Australia" 593   13.7806460366862 26.35 "AUS"  81.10959  .02256063
"Australia" 599 113.31114232412058 21.68 "AUS"  92.94691  .07643414
"Australia" 602   5.39159854157259 17.59 "AUS"  74.11694 .067879446
"Australia" 605   2.14742370510351 34.54 "AUS"   96.4537   .0859993
"Australia" 608   87.3472106214208  23.7 "AUS"  71.16855  .06681671
"Australia" 611 113.51002139037439 17.75 "AUS"   97.2039  .10574418
"Australia" 617   5.78119979840956 16.52 "AUS"  84.39185  .04680805
"Australia" 620   103.845486797982 42.96 "AUS"  83.35045  .05326705
"Australia" 623  78.23441274346507  23.4 "AUS"  85.34132  .03803438
"Australia" 626 105.05227538147368  15.5 "AUS"  88.75998  .06207325
"Australia" 629    5.0359998448912 17.08 "AUS"  77.82243  .04862903
"Australia" 632    2.2627500144816 15.73 "AUS"  69.28051  .07696255
"Australia" 635   52.7759976873558 18.02 "AUS"  74.83156  .04271513
"Australia" 636   300.074149024059 14.28 "AUS"  90.65749  .05504385
"Australia" 638   52.2718762485661  12.7 "AUS"  75.89672  .05318772
"Australia" 639 268.23269184508626 13.52 "AUS"  95.10787  .08235998
"Australia" 641    1.5695999874432 16.86 "AUS"  82.97301  .06088512
"Australia" 642   100.078281350129 13.45 "AUS" 73.975815   .0314169
"Australia" 643   89.3405374444399 17.01 "AUS"  90.95675   .0573943
"Australia" 644  241.3651685004295  16.6 "AUS" 100.45933   .0885092
"Australia" 645   13.5225288885003 13.75 "AUS"  72.95312  .05786828
"Australia" 646  15.96572103874107  13.7 "AUS"  77.77902  .04165878
"Australia" 647  512.6052086448998 13.72 "AUS"  70.37249  .08326395
"Australia" 648 114.84902322764196 18.41 "AUS"  77.91597 .035349566
"Australia" 649   271.020469064709    14 "AUS" 69.003525  .04562422
"Australia" 650  359.0666294984144 13.88 "AUS" 119.83855  .10790697
"Australia" 651  98.02835634580066 13.41 "AUS"  93.22154  .09070474
"Australia" 652  232.5659861929459  11.4 "AUS"  83.42886  .05644403
"Australia" 653 194.63592333650794 11.57 "AUS"  98.76711  .05155457
"Australia" 654  116.0144934710481 16.95 "AUS" 138.75395  .25103945
"Australia" 655 171.28658354401801 11.98 "AUS" 136.44559   .2193164
"Australia" 656  82.96476097395711 16.31 "AUS" 131.55666  .20873475
"Australia" 657  64.84887802121551 14.03 "AUS"    83.661  .06463387
"Australia" 658   5.00998866490065 13.33 "AUS"  84.38005    .177041
"Australia" 659  60.50562383797012  19.2 "AUS"  86.52622    .182887
"Australia" 661   57.2331826722868 13.34 "AUS"  111.9247     .15052
"Australia" 664     4.388000136028 13.84 "AUS"  76.26233  .04703065
"Australia" 666  178.6015880307878 12.12 "AUS" 106.80866  .07792545
"Australia" 667   43.9319118768444 28.43 "AUS"  82.47853  .04712091
"Australia" 670 196.37429852534285 16.13 "AUS" 150.71588  .12785389
"Australia" 671   143.391563179219 18.21 "AUS" 148.23688   .0983671
"Australia" 672   1.30843185534351  20.2 "AUS" 118.21114  .10659993
"Australia" 673   84.6417521461589 20.55 "AUS" 103.74965  .06360701
"Australia" 674   1.30404702722896 13.95 "AUS"  109.7203   .0452673
"Australia" 676   5.88378203262743 14.19 "AUS"  88.33242   .0546274
"Australia" 679   66.7919981405108 13.42 "AUS"  98.73849  .05685342
"Australia" 683   87.6354756831622 14.04 "AUS" 102.18047  .05904138
"Australia" 684  8.554879326540789 11.99 "AUS" 105.20123  .12662074
"Australia" 686   8.01794571752694 12.37 "AUS"  96.63588  .03845415
"Australia" 687   4.24192205168453 10.82 "AUS" 119.45992  .14710519
"Australia" 688   4.39733777154964 10.41 "AUS" 102.57996   .1320065
"Australia" 689   72.1832930084882 11.18 "AUS" 126.78902   .1857585
"Australia" 690 153.62090239796686 10.26 "AUS" 102.81346  .04768717
"Australia" 691    4.7810438330343 10.59 "AUS" 138.53162  .24033326
"Australia" 692 111.70518313712653  9.51 "AUS" 115.53445   .0848854
"Australia" 693   4.69987101388472 10.18 "AUS"  91.46157  .08116224
"Australia" 694  51.36725639916259 11.28 "AUS"  92.59602  .02945632
"Australia" 695  1.778994750187204 11.04 "AUS"  82.71825  .11422827
"Australia" 696  248.3902547631801 13.54 "AUS"  92.42679  .09110629
"Australia" 697   12.3776649722749 19.85 "AUS"  64.49137  .02789919
"Australia" 698   1.47677823905732 19.97 "AUS"  111.9623   .1522802
"Australia" 699  90.33450187760775 15.93 "AUS" 123.26347  .05182914
"Australia" 700   7.11197404158586 15.43 "AUS" 125.66662   .1512732
"Australia" 702   17.6040015069025 12.83 "AUS" 110.02843    .111002
"Australia" 703  131.0769489388529 12.86 "AUS" 112.74168   .0985354
"Australia" 704  198.5520440939224 12.12 "AUS"  75.05391  .04199916
"Australia" 705   75.6581388997094 21.23 "AUS"  92.01648  .07414731
"Australia" 706  179.5312180781125 18.07 "AUS"  80.40759   .0847709
"Australia" 707   3.43500175185089 25.42 "AUS"  90.60767   .1689744
"Australia" 708 48.837586721138344 16.57 "AUS"   87.4241  .14874628
"Australia" 709  71.34996415377798 14.78 "AUS"  96.80016 .066093855
"Australia" 710  96.87380211941274 13.71 "AUS"  82.32829  .19187453
"Australia" 711  130.4447755127963 13.12 "AUS"  79.25345  .11639359
"Australia" 712 135.63555332737047 18.71 "AUS"  106.4719  .16332847
"Australia" 713  49.53545530931814 15.08 "AUS" 106.58846  .11217534
"Australia" 714 252.21372529747921 16.12 "AUS"  93.52126  .15042916
"Australia" 715  8.845936399197878 18.98 "AUS" 103.40442  .18582426
"Australia" 717   4.38394869101505 13.22 "AUS"  97.83673  .08828723
"Australia" 718   66.0836227945159 12.62 "AUS" 73.078384  .11443662
"Australia" 719  85.58889638596006 13.78 "AUS"  74.27989  .11163965
"Australia" 720  28.88434734542577 18.84 "AUS" 138.42094  .19413193
"Australia" 721  122.8428718542637 40.11 "AUS"  75.95556  .08024967
"Australia" 722  111.6841827310382 53.54 "AUS"  81.54003  .12399521
end
format %tm Mdate
------------------ copy up to and including the previous line ------------------

Listed 100 out of 978 observations
Use the count() option to list more

.

Source:
For my source I used the following to generate a quarterly date

gen qdate = yq(Year, Quarters)
format qdate %tq

and ended up with this:

----------------------- copy starting from the next line -----------------------


	
	
* Example generated by -dataex-. For more info, type help dataex
clear
input int Year byte Quarters double GEF float qdate
1975 1   .348017309098343  60
1975 2   .350317895043664  61
1975 3   .346090481436026  62
1975 4   .353630754302994  63
1976 1   .313182259208404  64
1976 2   .309708468211529  65
1976 3   .255311373580155  66
1976 4   .273957335869496  67
1977 1   .247843145392628  68
1977 2   .206522461688371  69
1977 3   .239103062607169  70
1977 4   .266387758800504  71
1978 1   .226744808592191  72
1978 2     .2295822284189  73
1978 3   .225766807531485  74
1978 4   .167931946653422  75
1979 1   .193889326865204  76
1979 2   .014086688624344  77
1979 3 -.0827023832367562  78
1979 4 -.0691036731461301  79
1980 1 -.0838151202419258  80
1980 2  .0432253650380687  81
1980 3  .0180522296148671  82
1980 4   .088496139792411  83
1981 1  -.097504892824819  84
1981 2 -.0523054698925359  85
1981 3 -.0264698647207201  86
1981 4  -.109358498153985  87
1982 1  .0272661789469188  88
1982 2  .0544411258174124  89
1982 3  .0889675751344975  90
1982 4   .242898462592534  91
1983 1   .283383779782916  92
1983 2   .287893315399299  93
1983 3   .346408873209162  94
1983 4   .333580350610651  95
1984 1   .277794977794878  96
1984 2   .269521167409927  97
1984 3   .280190882400113  98
1984 4   .288454595171351  99
1985 1   .294831966132069 100
1985 2    .29047733221453 101
1985 3   .229249969959985 102
1985 4   .185735840911551 103
1986 1   .184920449833201 104
1986 2   .159267239166694 105
1986 3   .101674901706708 106
1986 4    .13936152886829 107
1987 1   .104736639440848 108
1987 2   .121197299251788 109
1987 3   .114251781155051 110
1987 4   .110104389133776 111
1988 1  .0799068743243151 112
1988 2  .0438768212749731 113
1988 3  .0604202231126318 114
1988 4   .036356803335416 115
1989 1 -.0127647737391928 116
1989 2 -.0306437161252737 117
1989 3  .0556934635582499 118
1989 4  .0582749128783206 119
1990 1 -.0187321463393172 120
1990 2  .0980868124162989 121
1990 3  .0711173179575554 122
1990 4  .0836228165301396 123
1991 1   .164871080095036 124
1991 2   .221147544937654 125
1991 3   .221257335684281 126
1991 4   .328600447586377 127
1992 1   .294087025891484 128
1992 2   .319810434456036 129
1992 3   .308755605287922 130
1992 4   .305718729844549 131
1993 1   .271830721066679 132
1993 2   .241056882629679 133
1993 3   .211876722143694 134
1993 4   .213902214056672 135
1994 1    .12467916364733 136
1994 2   .252203976958148 137
1994 3   .236739587427145 138
1994 4   .104553402206986 139
1995 1 -.0312747665095084 140
1995 2 -.0927330812280583 141
1995 3 -.0979039910781864 142
1995 4 -.0141196734224682 143
1996 1  -.200333565635611 144
1996 2  -.210695209174233 145
1996 3  -.206971686940951 146
1996 4  -.239768656855137 147
1997 1  -.286050838690992 148
1997 2  -.279419652853644 149
1997 3   -.31651929567759 150
1997 4  -.419700931918019 151
1998 1  -.403772057513179 152
1998 2   -.40370691632402 153
1998 3  -.412003134136664 154
1998 4  -.462542413486552 155
1999 1  -.456479196850361 156
1999 2  -.423993264670496 157
1999 3  -.456580396730076 158
1999 4  -.397021596445035 159
end
format %tq qdate
------------------ copy up to and including the previous line ------------------

Listed 100 out of 192 observations
Use the count() option to list more

.

My attempt gave me a weird result.

 merge m:1 qdate using "D:\STATA\GEF.dta"

---------------------- copy starting from the next line -----------------------


	
	
* Example generated by -dataex-. For more info, type help dataex
clear
input str24 Source str32 CountryName str3 country_code float Mdate double Industrial_FDI float(VIX GPR GPRC) int Year float qdate byte Quarters double GEF byte _merge
"Singapore"         "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"Denmark"           "Japan"       "JPN" 518 43.5801056817563 29.15  358.7112  .8148333 15795 5 . . 1
"Taiwan, China"     "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"Sweden"            "Thailand"    "THA" 518 132.971836356703 29.15  358.7112 .10254705 15795 5 . . 1
"USA"               "China"       "CHN" 518 65.3701585226344 29.15  358.7112  .8065187 15795 5 . . 1
"Japan"             "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"Republic of Korea" "Indonesia"   "IDN" 518 175.570848177807 29.15  358.7112  .1718356 15795 5 . . 1
"Republic of Korea" "China"       "CHN" 518 10.8950264204391 29.15  358.7112  .8065187 15795 5 . . 1
"Singapore"         "Australia"   "AUS" 518 65.3701585226344 29.15  358.7112  .4018736 15795 5 . . 1
"Singapore"         "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "Philippines" "PHL" 518 155.145434439179 29.15  358.7112 .20786564 15795 5 . . 1
"Malaysia"          "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"Belgium"           "Malaysia"    "MYS" 518 65.3701585226344 29.15  358.7112  .0997755 15795 5 . . 1
"Singapore"         "China"       "CHN" 518  202.25263496214 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "Japan"       "JPN" 518 4.35801056817563 29.15  358.7112  .8148333 15795 5 . . 1
"Switzerland"       "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"United Kingdom"    "South Korea" "KOR" 518 4.35801056817563 29.15  358.7112  .8730357 15795 5 . . 1
"Japan"             "Philippines" "PHL" 518  202.25263496214 29.15  358.7112 .20786564 15795 5 . . 1
"USA"               "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5 . . 1
"Japan"             "Hong Kong"   "HKG" 518 4.35801056817563 29.15  358.7112 .12194784 15795 5 . . 1
"Thailand"          "Hong Kong"   "HKG" 518 155.145434439179 29.15  358.7112 .12194784 15795 5 . . 1
"Japan"             "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"Republic of Korea" "China"       "CHN" 518 65.3701585226344 29.15  358.7112  .8065187 15795 5 . . 1
"Taiwan, China"     "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"Japan"             "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"Republic of Korea" "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"Hong Kong"         "China"       "CHN" 518 10.9407223402517 29.15  358.7112  .8065187 15795 5 . . 1
"Taiwan, China"     "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"Japan"             "China"       "CHN" 518 65.3701585226344 29.15  358.7112  .8065187 15795 5 . . 1
"Netherlands"       "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"Finland"           "China"       "CHN" 518  202.25263496214 29.15  358.7112  .8065187 15795 5 . . 1
"Sweden"            "South Korea" "KOR" 518 4.35801056817563 29.15  358.7112  .8730357 15795 5 . . 1
"Malaysia"          "Thailand"    "THA" 518 175.570848177807 29.15  358.7112 .10254705 15795 5 . . 1
"Switzerland"       "China"       "CHN" 518 13.1191512774418 29.15  358.7112  .8065187 15795 5 . . 1
"Republic of Korea" "China"       "CHN" 518 10.8950264204391 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5 . . 1
"United Kingdom"    "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5 . . 1
"Denmark"           "Japan"       "JPN" 518 132.971836356703 29.15  358.7112  .8148333 15795 5 . . 1
"USA"               "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5 . . 1
"Taiwan, China"     "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"Germany"           "China"       "CHN" 518 10.8950264204391 29.15  358.7112  .8065187 15795 5 . . 1
"Japan"             "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"Sweden"            "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"France"            "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"Japan"             "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5 . . 1
"Japan"             "Philippines" "PHL" 518 175.570848177807 29.15  358.7112 .20786564 15795 5 . . 1
"USA"               "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 1.90662962357684 29.15  358.7112  .8065187 15795 5 . . 1
"Republic of Korea" "China"       "CHN" 518 175.570848177807 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "Japan"       "JPN" 518 1.90662962357684 29.15  358.7112  .8148333 15795 5 . . 1
"Republic of Korea" "China"       "CHN" 518 10.8950264204391 29.15  358.7112  .8065187 15795 5 . . 1
"Germany"           "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "Philippines" "PHL" 518 155.145434439179 29.15  358.7112 .20786564 15795 5 . . 1
"USA"               "Japan"       "JPN" 518 155.145434439179 29.15  358.7112  .8148333 15795 5 . . 1
"Singapore"         "China"       "CHN" 518 10.8950264204391 29.15  358.7112  .8065187 15795 5 . . 1
"Republic of Korea" "China"       "CHN" 518 175.570848177807 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"Japan"             "China"       "CHN" 518  202.25263496214 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 65.3701585226344 29.15  358.7112  .8065187 15795 5 . . 1
"Germany"           "South Korea" "KOR" 518 4.35801056817563 29.15  358.7112  .8730357 15795 5 . . 1
"Germany"           "Hong Kong"   "HKG" 518 4.35801056817563 29.15  358.7112 .12194784 15795 5 . . 1
"Taiwan, China"     "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"Taiwan, China"     "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "Hong Kong"   "HKG" 518 4.35801056817563 29.15  358.7112 .12194784 15795 5 . . 1
"Japan"             "China"       "CHN" 518 13.1191512774418 29.15  358.7112  .8065187 15795 5 . . 1
"Sweden"            "Australia"   "AUS" 518 4.35801056817563 29.15  358.7112  .4018736 15795 5 . . 1
"Taiwan, China"     "China"       "CHN" 518 43.5801056817563 29.15  358.7112  .8065187 15795 5 . . 1
"Sweden"            "Australia"   "AUS" 518 83.7887410796971 29.15  358.7112  .4018736 15795 5 . . 1
"Taiwan, China"     "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"Germany"           "China"       "CHN" 521 4.57090618215061 19.52 145.76973  .4519842 15886 5 . . 1
"Japan"             "China"       "CHN" 521 184.147757970518 19.52 145.76973  .4519842 15886 5 . . 1
"Republic of Korea" "China"       "CHN" 521 139.467717975089 19.52 145.76973  .4519842 15886 5 . . 1
"Republic of Korea" "China"       "CHN" 521 139.467717975089 19.52 145.76973  .4519842 15886 5 . . 1
"Switzerland"       "Hong Kong"   "HKG" 521 4.57090618215061 19.52 145.76973 .04821165 15886 5 . . 1
"Switzerland"       "China"       "CHN" 521 45.7090618215061 19.52 145.76973  .4519842 15886 5 . . 1
"Japan"             "Indonesia"   "IDN" 521 162.724530910753 19.52 145.76973 .12655558 15886 5 . . 1
"USA"               "Australia"   "AUS" 521 87.8819517769398 19.52 145.76973 .14463495 15886 5 . . 1
"USA"               "Japan"       "JPN" 521 1.99977145469089 19.52 145.76973  .3947329 15886 5 . . 1
"Republic of Korea" "Thailand"    "THA" 521 139.467717975089 19.52 145.76973 .11450267 15886 5 . . 1
"Netherlands"       "China"       "CHN" 521 139.467717975089 19.52 145.76973  .4519842 15886 5 . . 1
"USA"               "China"       "CHN" 521 4.57090618215061 19.52 145.76973  .4519842 15886 5 . . 1
"USA"               "Hong Kong"   "HKG" 521 57.1363272768826 19.52 145.76973 .04821165 15886 5 . . 1
"Japan"             "Thailand"    "THA" 521 212.132991658096 19.52 145.76973 .11450267 15886 5 . . 1
"Japan"             "China"       "CHN" 521 139.467717975089 19.52 145.76973  .4519842 15886 5 . . 1
"Japan"             "China"       "CHN" 521 162.724530910753 19.52 145.76973  .4519842 15886 5 . . 1
"USA"               "China"       "CHN" 521 162.724530910753 19.52 145.76973  .4519842 15886 5 . . 1
"Republic of Korea" "China"       "CHN" 521 139.467717975089 19.52 145.76973  .4519842 15886 5 . . 1
"USA"               "Japan"       "JPN" 521 212.132991658096 19.52 145.76973  .3947329 15886 5 . . 1
"Japan"             "Thailand"    "THA" 521 162.724530910753 19.52 145.76973 .11450267 15886 5 . . 1
"USA"               "Hong Kong"   "HKG" 521 1.99977145469089 19.52 145.76973 .04821165 15886 5 . . 1
"USA"               "China"       "CHN" 521 68.5635927322592 19.52 145.76973  .4519842 15886 5 . . 1
"Taiwan, China"     "China"       "CHN" 521 139.467717975089 19.52 145.76973  .4519842 15886 5 . . 1
end
format %tm Mdate
format %tdnn/dd/CCYY Year
format %tq qdate
label values _merge _merge
label def _merge 1 "Master only (1)", modify
------------------ copy up to and including the previous line ------------------

Listed 100 out of 4316 observations
Use the count() option to list more

I tried using 1:1 and 1:m but stata gave me this error

variable qdate does not uniquely identify observations in the master data
r(459);

When I used m:1 my dates now make no sense and I can't trust that my dataset is aligned properly.

I'd be grateful for your help.
Thank you

[Clyde Schechter]

I take it that what you want to accomplish is getting the value of GEF from your "source" dataset, into the master data set with each observation from "source" pairing up with all of the months that are part of the quarter in the "source" file.

I have no idea how you got the result you did with your attempts at -merge-ing because after you create qdate in your source data set(which, make no mistake, was the right thing to do), there is still no qdate in the master data set to -merge- it with. I would have expected that the error you got would be that variable qdate is not found in the master data set. So you have to also create a qdate variable in your master data set. You cannot do it directly from Mdate: you have to pass it through a daily date first because Stata has no function to directly create a quarterly date from a monthly date.

Anyway, here is how to accomplish what you seek:

use `master', clear
gen qdate = qofd(dofm(Mdate))
format qdate %tq

merge m:1 qdate using `source'

Now, in your example data, the results are rather unsatisfying, but that is because your examples do not contain any dates that could match up. The source data are all from years 1975-1999, whereas in the master data set the dates are all in years 2003-2020, so there are no possible matches here. But I assume that in your full data sets you will indeed get matches.

[Nour Mohamed]

I take it that what you want to accomplish is getting the value of GEF from your "source" dataset, into the master data set with each observation from "source" pairing up with all of the months that are part of the quarter in the "source" file.

I have no idea how you got the result you did with your attempts at -merge-ing because after you create qdate in your source data set(which, make no mistake, was the right thing to do), there is still no qdate in the master data set to -merge- it with. I would have expected that the error you got would be that variable qdate is not found in the master data set. So you have to also create a qdate variable in your master data set. You cannot do it directly from Mdate: you have to pass it through a daily date first because Stata has no function to directly create a quarterly date from a monthly date.

Anyway, here is how to accomplish what you seek:

use `master', clear
gen qdate = qofd(dofm(Mdate))
format qdate %tq

merge m:1 qdate using `source'

Now, in your example data, the results are rather unsatisfying, but that is because your examples do not contain any dates that could match up. The source data are all from years 1975-1999, whereas in the master data set the dates are all in years 2003-2020, so there are no possible matches here. But I assume that in your full data sets you will indeed get matches.

With the attempt I showed I generated a quarterly variable for my master dataset, yes, which is how i was able to get my attempt with the third dataset in my original comment.
I was unsure that maybe something wrong happened halfway so I was retracing my steps, I showed my master data without the qdate.

. dataex

----------------------- copy starting from the next line -----------------------


	
	
* Example generated by -dataex-. For more info, type help dataex
clear
input str24 Source str32 CountryName str3 country_code float Mdate double Industrial_FDI float(VIX GPR GPRC) int Year float qdate
"Singapore"         "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5
"USA"               "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5
"USA"               "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5
"USA"               "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5
"Denmark"           "Japan"       "JPN" 518 43.5801056817563 29.15  358.7112  .8148333 15795 5
"Taiwan, China"     "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5
"Sweden"            "Thailand"    "THA" 518 132.971836356703 29.15  358.7112 .10254705 15795 5
"USA"               "China"       "CHN" 518 65.3701585226344 29.15  358.7112  .8065187 15795 5
"Japan"             "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5
"USA"               "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5
"Republic of Korea" "Indonesia"   "IDN" 518 175.570848177807 29.15  358.7112  .1718356 15795 5
"Republic of Korea" "China"       "CHN" 518 10.8950264204391 29.15  358.7112  .8065187 15795 5
"Singapore"         "Australia"   "AUS" 518 65.3701585226344 29.15  358.7112  .4018736 15795 5
"Singapore"         "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5
"USA"               "Philippines" "PHL" 518 155.145434439179 29.15  358.7112 .20786564 15795 5
"Malaysia"          "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5
"Belgium"           "Malaysia"    "MYS" 518 65.3701585226344 29.15  358.7112  .0997755 15795 5
"Singapore"         "China"       "CHN" 518  202.25263496214 29.15  358.7112  .8065187 15795 5
"USA"               "Japan"       "JPN" 518 4.35801056817563 29.15  358.7112  .8148333 15795 5
"Switzerland"       "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5
"United Kingdom"    "South Korea" "KOR" 518 4.35801056817563 29.15  358.7112  .8730357 15795 5
"Japan"             "Philippines" "PHL" 518  202.25263496214 29.15  358.7112 .20786564 15795 5
"USA"               "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5
"Japan"             "Hong Kong"   "HKG" 518 4.35801056817563 29.15  358.7112 .12194784 15795 5
"Thailand"          "Hong Kong"   "HKG" 518 155.145434439179 29.15  358.7112 .12194784 15795 5
"Japan"             "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5
"Republic of Korea" "China"       "CHN" 518 65.3701585226344 29.15  358.7112  .8065187 15795 5
"Taiwan, China"     "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5
"Japan"             "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5
"USA"               "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5
"Republic of Korea" "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5
"Hong Kong"         "China"       "CHN" 518 10.9407223402517 29.15  358.7112  .8065187 15795 5
"Taiwan, China"     "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5
"USA"               "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5
"Japan"             "China"       "CHN" 518 65.3701585226344 29.15  358.7112  .8065187 15795 5
"Netherlands"       "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5
"USA"               "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5
"Finland"           "China"       "CHN" 518  202.25263496214 29.15  358.7112  .8065187 15795 5
"Sweden"            "South Korea" "KOR" 518 4.35801056817563 29.15  358.7112  .8730357 15795 5
"Malaysia"          "Thailand"    "THA" 518 175.570848177807 29.15  358.7112 .10254705 15795 5
"Switzerland"       "China"       "CHN" 518 13.1191512774418 29.15  358.7112  .8065187 15795 5
"Republic of Korea" "China"       "CHN" 518 10.8950264204391 29.15  358.7112  .8065187 15795 5
"USA"               "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5
"United Kingdom"    "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5
"Denmark"           "Japan"       "JPN" 518 132.971836356703 29.15  358.7112  .8148333 15795 5
"USA"               "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5
"Taiwan, China"     "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5
"USA"               "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5
"Germany"           "China"       "CHN" 518 10.8950264204391 29.15  358.7112  .8065187 15795 5
"Japan"             "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5
"Sweden"            "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5
"France"            "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5
"Japan"             "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5
"Japan"             "Philippines" "PHL" 518 175.570848177807 29.15  358.7112 .20786564 15795 5
"USA"               "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5
"USA"               "China"       "CHN" 518 1.90662962357684 29.15  358.7112  .8065187 15795 5
"Republic of Korea" "China"       "CHN" 518 175.570848177807 29.15  358.7112  .8065187 15795 5
"USA"               "Japan"       "JPN" 518 1.90662962357684 29.15  358.7112  .8148333 15795 5
"Republic of Korea" "China"       "CHN" 518 10.8950264204391 29.15  358.7112  .8065187 15795 5
"Germany"           "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5
"USA"               "Philippines" "PHL" 518 155.145434439179 29.15  358.7112 .20786564 15795 5
"USA"               "Japan"       "JPN" 518 155.145434439179 29.15  358.7112  .8148333 15795 5
"Singapore"         "China"       "CHN" 518 10.8950264204391 29.15  358.7112  .8065187 15795 5
"Republic of Korea" "China"       "CHN" 518 175.570848177807 29.15  358.7112  .8065187 15795 5
"USA"               "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5
"Japan"             "China"       "CHN" 518  202.25263496214 29.15  358.7112  .8065187 15795 5
"USA"               "China"       "CHN" 518 65.3701585226344 29.15  358.7112  .8065187 15795 5
"Germany"           "South Korea" "KOR" 518 4.35801056817563 29.15  358.7112  .8730357 15795 5
"Germany"           "Hong Kong"   "HKG" 518 4.35801056817563 29.15  358.7112 .12194784 15795 5
"Taiwan, China"     "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5
"Taiwan, China"     "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5
"USA"               "Hong Kong"   "HKG" 518 4.35801056817563 29.15  358.7112 .12194784 15795 5
"Japan"             "China"       "CHN" 518 13.1191512774418 29.15  358.7112  .8065187 15795 5
"Sweden"            "Australia"   "AUS" 518 4.35801056817563 29.15  358.7112  .4018736 15795 5
"Taiwan, China"     "China"       "CHN" 518 43.5801056817563 29.15  358.7112  .8065187 15795 5
"Sweden"            "Australia"   "AUS" 518 83.7887410796971 29.15  358.7112  .4018736 15795 5
"Taiwan, China"     "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5
"Germany"           "China"       "CHN" 521 4.57090618215061 19.52 145.76973  .4519842 15886 5
"Japan"             "China"       "CHN" 521 184.147757970518 19.52 145.76973  .4519842 15886 5
"Republic of Korea" "China"       "CHN" 521 139.467717975089 19.52 145.76973  .4519842 15886 5
"Republic of Korea" "China"       "CHN" 521 139.467717975089 19.52 145.76973  .4519842 15886 5
"Switzerland"       "Hong Kong"   "HKG" 521 4.57090618215061 19.52 145.76973 .04821165 15886 5
"Switzerland"       "China"       "CHN" 521 45.7090618215061 19.52 145.76973  .4519842 15886 5
"Japan"             "Indonesia"   "IDN" 521 162.724530910753 19.52 145.76973 .12655558 15886 5
"USA"               "Australia"   "AUS" 521 87.8819517769398 19.52 145.76973 .14463495 15886 5
"USA"               "Japan"       "JPN" 521 1.99977145469089 19.52 145.76973  .3947329 15886 5
"Republic of Korea" "Thailand"    "THA" 521 139.467717975089 19.52 145.76973 .11450267 15886 5
"Netherlands"       "China"       "CHN" 521 139.467717975089 19.52 145.76973  .4519842 15886 5
"USA"               "China"       "CHN" 521 4.57090618215061 19.52 145.76973  .4519842 15886 5
"USA"               "Hong Kong"   "HKG" 521 57.1363272768826 19.52 145.76973 .04821165 15886 5
"Japan"             "Thailand"    "THA" 521 212.132991658096 19.52 145.76973 .11450267 15886 5
"Japan"             "China"       "CHN" 521 139.467717975089 19.52 145.76973  .4519842 15886 5
"Japan"             "China"       "CHN" 521 162.724530910753 19.52 145.76973  .4519842 15886 5
"USA"               "China"       "CHN" 521 162.724530910753 19.52 145.76973  .4519842 15886 5
"Republic of Korea" "China"       "CHN" 521 139.467717975089 19.52 145.76973  .4519842 15886 5
"USA"               "Japan"       "JPN" 521 212.132991658096 19.52 145.76973  .3947329 15886 5
"Japan"             "Thailand"    "THA" 521 162.724530910753 19.52 145.76973 .11450267 15886 5
"USA"               "Hong Kong"   "HKG" 521 1.99977145469089 19.52 145.76973 .04821165 15886 5
"USA"               "China"       "CHN" 521 68.5635927322592 19.52 145.76973  .4519842 15886 5
"Taiwan, China"     "China"       "CHN" 521 139.467717975089 19.52 145.76973  .4519842 15886 5
end
format %tm Mdate
format %tdnn/dd/CCYY Year
format %tq qdate
------------------ copy up to and including the previous line ------------------

Listed 100 out of 4124 observations
Use the count() option to list more

[Nour Mohamed]

I take it that what you want to accomplish is getting the value of GEF from your "source" dataset, into the master data set with each observation from "source" pairing up with all of the months that are part of the quarter in the "source" file.

I have no idea how you got the result you did with your attempts at -merge-ing because after you create qdate in your source data set(which, make no mistake, was the right thing to do), there is still no qdate in the master data set to -merge- it with. I would have expected that the error you got would be that variable qdate is not found in the master data set. So you have to also create a qdate variable in your master data set. You cannot do it directly from Mdate: you have to pass it through a daily date first because Stata has no function to directly create a quarterly date from a monthly date.

Anyway, here is how to accomplish what you seek:

use `master', clear
gen qdate = qofd(dofm(Mdate))
format qdate %tq

merge m:1 qdate using `source'

Now, in your example data, the results are rather unsatisfying, but that is because your examples do not contain any dates that could match up. The source data are all from years 1975-1999, whereas in the master data set the dates are all in years 2003-2020, so there are no possible matches here. But I assume that in your full data sets you will indeed get matches.

Should I delete the excess data in my source dataset? Is that what's causing my unsatisfying results?

[Nour Mohamed]

I tried running after dropping the excess years in my source material, my data doesn't match up and the merge generate earlier data that starts from 1960

 merge m:1 qdate using "D:\STATA\Source"

    Result                      Number of obs
    -----------------------------------------
    Not matched                         4,204
        from master                     4,124  (_merge==1)
        from using                         80  (_merge==2)

    Matched                                 0  (_merge==3)
    -----------------------------------------

. dataex

----------------------- copy starting from the next line -----------------------


	
	
* Example generated by -dataex-. For more info, type help dataex
clear
input str24 Source str32 CountryName str3 country_code float Mdate double Industrial_FDI float(VIX GPR GPRC) int Year float qdate byte Quarters double GEF byte _merge
"Singapore"         "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"Denmark"           "Japan"       "JPN" 518 43.5801056817563 29.15  358.7112  .8148333 15795 5 . . 1
"Taiwan, China"     "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"Sweden"            "Thailand"    "THA" 518 132.971836356703 29.15  358.7112 .10254705 15795 5 . . 1
"USA"               "China"       "CHN" 518 65.3701585226344 29.15  358.7112  .8065187 15795 5 . . 1
"Japan"             "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"Republic of Korea" "Indonesia"   "IDN" 518 175.570848177807 29.15  358.7112  .1718356 15795 5 . . 1
"Republic of Korea" "China"       "CHN" 518 10.8950264204391 29.15  358.7112  .8065187 15795 5 . . 1
"Singapore"         "Australia"   "AUS" 518 65.3701585226344 29.15  358.7112  .4018736 15795 5 . . 1
"Singapore"         "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "Philippines" "PHL" 518 155.145434439179 29.15  358.7112 .20786564 15795 5 . . 1
"Malaysia"          "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"Belgium"           "Malaysia"    "MYS" 518 65.3701585226344 29.15  358.7112  .0997755 15795 5 . . 1
"Singapore"         "China"       "CHN" 518  202.25263496214 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "Japan"       "JPN" 518 4.35801056817563 29.15  358.7112  .8148333 15795 5 . . 1
"Switzerland"       "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"United Kingdom"    "South Korea" "KOR" 518 4.35801056817563 29.15  358.7112  .8730357 15795 5 . . 1
"Japan"             "Philippines" "PHL" 518  202.25263496214 29.15  358.7112 .20786564 15795 5 . . 1
"USA"               "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5 . . 1
"Japan"             "Hong Kong"   "HKG" 518 4.35801056817563 29.15  358.7112 .12194784 15795 5 . . 1
"Thailand"          "Hong Kong"   "HKG" 518 155.145434439179 29.15  358.7112 .12194784 15795 5 . . 1
"Japan"             "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"Republic of Korea" "China"       "CHN" 518 65.3701585226344 29.15  358.7112  .8065187 15795 5 . . 1
"Taiwan, China"     "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"Japan"             "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"Republic of Korea" "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"Hong Kong"         "China"       "CHN" 518 10.9407223402517 29.15  358.7112  .8065187 15795 5 . . 1
"Taiwan, China"     "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"Japan"             "China"       "CHN" 518 65.3701585226344 29.15  358.7112  .8065187 15795 5 . . 1
"Netherlands"       "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"Finland"           "China"       "CHN" 518  202.25263496214 29.15  358.7112  .8065187 15795 5 . . 1
"Sweden"            "South Korea" "KOR" 518 4.35801056817563 29.15  358.7112  .8730357 15795 5 . . 1
"Malaysia"          "Thailand"    "THA" 518 175.570848177807 29.15  358.7112 .10254705 15795 5 . . 1
"Switzerland"       "China"       "CHN" 518 13.1191512774418 29.15  358.7112  .8065187 15795 5 . . 1
"Republic of Korea" "China"       "CHN" 518 10.8950264204391 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5 . . 1
"United Kingdom"    "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5 . . 1
"Denmark"           "Japan"       "JPN" 518 132.971836356703 29.15  358.7112  .8148333 15795 5 . . 1
"USA"               "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5 . . 1
"Taiwan, China"     "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"Germany"           "China"       "CHN" 518 10.8950264204391 29.15  358.7112  .8065187 15795 5 . . 1
"Japan"             "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"Sweden"            "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"France"            "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"Japan"             "China"       "CHN" 518 4.35801056817563 29.15  358.7112  .8065187 15795 5 . . 1
"Japan"             "Philippines" "PHL" 518 175.570848177807 29.15  358.7112 .20786564 15795 5 . . 1
"USA"               "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 1.90662962357684 29.15  358.7112  .8065187 15795 5 . . 1
"Republic of Korea" "China"       "CHN" 518 175.570848177807 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "Japan"       "JPN" 518 1.90662962357684 29.15  358.7112  .8148333 15795 5 . . 1
"Republic of Korea" "China"       "CHN" 518 10.8950264204391 29.15  358.7112  .8065187 15795 5 . . 1
"Germany"           "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "Philippines" "PHL" 518 155.145434439179 29.15  358.7112 .20786564 15795 5 . . 1
"USA"               "Japan"       "JPN" 518 155.145434439179 29.15  358.7112  .8148333 15795 5 . . 1
"Singapore"         "China"       "CHN" 518 10.8950264204391 29.15  358.7112  .8065187 15795 5 . . 1
"Republic of Korea" "China"       "CHN" 518 175.570848177807 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"Japan"             "China"       "CHN" 518  202.25263496214 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "China"       "CHN" 518 65.3701585226344 29.15  358.7112  .8065187 15795 5 . . 1
"Germany"           "South Korea" "KOR" 518 4.35801056817563 29.15  358.7112  .8730357 15795 5 . . 1
"Germany"           "Hong Kong"   "HKG" 518 4.35801056817563 29.15  358.7112 .12194784 15795 5 . . 1
"Taiwan, China"     "China"       "CHN" 518 132.971836356703 29.15  358.7112  .8065187 15795 5 . . 1
"Taiwan, China"     "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"USA"               "Hong Kong"   "HKG" 518 4.35801056817563 29.15  358.7112 .12194784 15795 5 . . 1
"Japan"             "China"       "CHN" 518 13.1191512774418 29.15  358.7112  .8065187 15795 5 . . 1
"Sweden"            "Australia"   "AUS" 518 4.35801056817563 29.15  358.7112  .4018736 15795 5 . . 1
"Taiwan, China"     "China"       "CHN" 518 43.5801056817563 29.15  358.7112  .8065187 15795 5 . . 1
"Sweden"            "Australia"   "AUS" 518 83.7887410796971 29.15  358.7112  .4018736 15795 5 . . 1
"Taiwan, China"     "China"       "CHN" 518 155.145434439179 29.15  358.7112  .8065187 15795 5 . . 1
"Germany"           "China"       "CHN" 521 4.57090618215061 19.52 145.76973  .4519842 15886 5 . . 1
"Japan"             "China"       "CHN" 521 184.147757970518 19.52 145.76973  .4519842 15886 5 . . 1
"Republic of Korea" "China"       "CHN" 521 139.467717975089 19.52 145.76973  .4519842 15886 5 . . 1
"Republic of Korea" "China"       "CHN" 521 139.467717975089 19.52 145.76973  .4519842 15886 5 . . 1
"Switzerland"       "Hong Kong"   "HKG" 521 4.57090618215061 19.52 145.76973 .04821165 15886 5 . . 1
"Switzerland"       "China"       "CHN" 521 45.7090618215061 19.52 145.76973  .4519842 15886 5 . . 1
"Japan"             "Indonesia"   "IDN" 521 162.724530910753 19.52 145.76973 .12655558 15886 5 . . 1
"USA"               "Australia"   "AUS" 521 87.8819517769398 19.52 145.76973 .14463495 15886 5 . . 1
"USA"               "Japan"       "JPN" 521 1.99977145469089 19.52 145.76973  .3947329 15886 5 . . 1
"Republic of Korea" "Thailand"    "THA" 521 139.467717975089 19.52 145.76973 .11450267 15886 5 . . 1
"Netherlands"       "China"       "CHN" 521 139.467717975089 19.52 145.76973  .4519842 15886 5 . . 1
"USA"               "China"       "CHN" 521 4.57090618215061 19.52 145.76973  .4519842 15886 5 . . 1
"USA"               "Hong Kong"   "HKG" 521 57.1363272768826 19.52 145.76973 .04821165 15886 5 . . 1
"Japan"             "Thailand"    "THA" 521 212.132991658096 19.52 145.76973 .11450267 15886 5 . . 1
"Japan"             "China"       "CHN" 521 139.467717975089 19.52 145.76973  .4519842 15886 5 . . 1
"Japan"             "China"       "CHN" 521 162.724530910753 19.52 145.76973  .4519842 15886 5 . . 1
"USA"               "China"       "CHN" 521 162.724530910753 19.52 145.76973  .4519842 15886 5 . . 1
"Republic of Korea" "China"       "CHN" 521 139.467717975089 19.52 145.76973  .4519842 15886 5 . . 1
"USA"               "Japan"       "JPN" 521 212.132991658096 19.52 145.76973  .3947329 15886 5 . . 1
"Japan"             "Thailand"    "THA" 521 162.724530910753 19.52 145.76973 .11450267 15886 5 . . 1
"USA"               "Hong Kong"   "HKG" 521 1.99977145469089 19.52 145.76973 .04821165 15886 5 . . 1
"USA"               "China"       "CHN" 521 68.5635927322592 19.52 145.76973  .4519842 15886 5 . . 1
"Taiwan, China"     "China"       "CHN" 521 139.467717975089 19.52 145.76973  .4519842 15886 5 . . 1
end
format %tm Mdate
format %tdnn/dd/CCYY Year
format %tq qdate
label values _merge _merge
label def _merge 1 "Master only (1)", modify
------------------ copy up to and including the previous line ------------------

Listed 100 out of 4204 observations
Use the count() option to list more

.

[Clyde Schechter]

In the example you show in #5, the values of qdate are wrong. That's probably why you are not getting any matches. The correct value of qdate in the master data is calculated by:

gen qdate = qofd(dofm(Mdate))
format qdate %tq

I don't know what you did, but it clearly wasn't that.

I don't know what you mean by getting rid of the excess data. If you mean removing variables in the source data set that you aren't going to use, that's not going to affect the -merge-ing. It will save you memory, and if you are running this on very large data sets, that might be an important consideration. But it won't help you with the -merge-.

If you mean getting rid of observations from the source data set that have values of qdate that are too early or too late to possibly match anything in the master data set, that will speed up the -merge- a little bit, but probably not all that much. It certainly won't make the -merge- more "correct." You can always eliminate those observations during or after the -merge-. For example, you might change the -merge- command to:

merge m:1 qdate using `source_data', keep(match master)

That will tell Stata not to include in the merged result any observations from `source_data' that don't match anything in the master. It's a slightly more convenient way of doing it, and it save you the trouble of having to figure out in advance which observations to remove.

[Nick Cox]

It's already been pointed out that the variable called Year is really a daily date. That misnaming is likely to cause problems of some kind, even if it is only giving a poor impression to people tasked with evaluating or extending your work.

See https://www.statalist.org/forums/for...o-fill-them-in #4

[Nour Mohamed]

In the example you show in #5, the values of qdate are wrong. That's probably why you are not getting any matches. The correct value of qdate in the master data is calculated by:

gen qdate = qofd(dofm(Mdate))
format qdate %tq

I don't know what you did, but it clearly wasn't that.

I don't know what you mean by getting rid of the excess data. If you mean removing variables in the source data set that you aren't going to use, that's not going to affect the -merge-ing. It will save you memory, and if you are running this on very large data sets, that might be an important consideration. But it won't help you with the -merge-.

If you mean getting rid of observations from the source data set that have values of qdate that are too early or too late to possibly match anything in the master data set, that will speed up the -merge- a little bit, but probably not all that much. It certainly won't make the -merge- more "correct." You can always eliminate those observations during or after the -merge-. For example, you might change the -merge- command to:

merge m:1 qdate using `source_data', keep(match master)

That will tell Stata not to include in the merged result any observations from `source_data' that don't match anything in the master. It's a slightly more convenient way of doing it, and it save you the trouble of having to figure out in advance which observations to remove.

That fixed!
Thank you so much.

To generate the quarterly data I had used

gen qdate = qofd(Mdate) 
format qdate %tq

I'm unsure why this caused the mess in the first place.

[Nick Cox]

First read the help and also unpack the function name: qofd() means quarterly date reduction of daily date.

You must feed it a daily date. If you feed it a monthly date, almost always the result is complete nonsense,

There is just one exception so far as I can see daily date -1 means 31 December 1959, which corresponds to monthly date -1, December 1959, and quarterly date -1, 1959q4.

In your data example you have monthly dates 518 and 521. They correspond to quarters in 2003, but to get there you need both the right functions and the right display format.

clear 
input mdate 
518
521
end

gen wrong = qofd(mdate)

gen right = qofd(dofm(mdate))

format wrong right %tq 

format mdate %tm 

list 

     +--------------------------+
     |  mdate    wrong    right |
     |--------------------------|
  1. | 2003m3   1961q2   2003q1 |
  2. | 2003m6   1961q2   2003q2 |
     +--------------------------+

[Nick Cox]

Also 1 January 1960 at 0.