Merging Two Excel Lists: Date does not uniquely identify observations in the master data

[Linus Tham]

Good Afternoon,

I'm trying to merge two excel spreadsheets by the variable Date. As a beginning Stata user, I'm having some difficulties with this endeavor where the error message is "Date does not uniquely identify observations in the master data" Would appreciate it if the more experienced members of this forum could provide some guidance in this regard. Am attaching the code (in a .do file) and the data sets.

Thank you.

Linus

[Clyde Schechter]

Please read the Forum FAQ, with special attention to #12, for excellent advice about showing example data and code you have tried. Among the things you will learn there are:

1. Attaching spreadsheets is deprecated. First, if you have not yet imported your data into Stata, it is premature to ask for help with code. Example data should come from your Stata data sets. Once you have accomplished that, the -dataex- command is the preferred way to show example data. If you are running version 15.1 or a fully updated version 14.2, it is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

2. The preferred way to show example code is to paste it into the Forum editor between code delimiters.

[William Lisowski]

Stata has told you that Date has repeated values in at least one of your datasets. You should import each worksheet from Stata's command line and then run commands such as

isid Date
duplicates list Date
count if missing(Date)
list if missing(Date)

to confirm that the data you are importing has the characteristics you expect.

I think you are overly optimistic about your success in importing your Excel spreadsheets, which may have material other than the data of interesting included on the worksheet. The commands I suggest will help confirm or refute this assertion.

And please, do follow Clyde's advice, and that of the Statalist forum, and read the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post.

[Linus Tham]

Hi Clyde and William,

Thank you for your advice.

Here's what I have using the datax command

* Example generated by -dataex-. To install: ssc install dataex
clear
input int Date double(stBU2 ndBU2 rdBU2 thBU2 stBU3 ndBU3 rdBU3 thBU3 stBU5 ndBU5 rdBU5 thBU5) byte(N O P Q R S T U V W X Y Z AA AB AC AD AE AF)
16040      .      .      .      .      .      .      .      .      .      .      .      . . . . . . . . . . . . . . . . . . . .
16041   4.25   4.75   4.94   4.75   4.04   4.29   4.37   4.17   3.25   3.33    3.3   3.12 . . . . . . . . . . . . . . . . . . .
16042  -3.19  -3.56  -3.69   -3.5     -3  -3.21  -3.29  -3.17   -2.8  -2.93  -2.97   -2.9 . . . . . . . . . . . . . . . . . . .
16043   5.25   5.25   5.06      5   5.29   5.46    5.5   5.58   6.18    6.3   6.32   6.35 . . . . . . . . . . . . . . . . . . .
16044  22.62  24.63   25.5  25.43  22.76  23.88  24.25  24.04   21.6  22.05  22.05  21.71 . . . . . . . . . . . . . . . . . . .
16047  -4.25  -4.88  -5.43  -5.87  -5.05  -5.46  -5.79  -6.08  -5.73  -5.97  -6.18  -6.36 . . . . . . . . . . . . . . . . . . .
16048  -7.12  -8.19  -8.94   -9.5  -8.16     -9  -9.58    -10   -9.3  -9.81 -10.14 -10.39 . . . . . . . . . . . . . . . . . . .
16049   6.19   6.82      7      7   6.37   6.75   6.75   6.58    5.9   6.08   6.05   5.92 . . . . . . . . . . . . . . . . . . .
16050  14.75  16.24  16.87  17.06  15.09  15.92  16.16  16.13  14.15  14.45   14.4  14.17 . . . . . . . . . . . . . . . . . . .
16051   -.57   -.37   -.06    .06   -.34   -.25   -.04    .04   -.22    -.2    -.1   -.07 . . . . . . . . . . . . . . . . . . .
16054  -2.56  -3.19  -3.94   -4.5  -3.37   -3.8  -4.29  -4.66  -4.18   -4.5  -4.88   -5.2 . . . . . . . . . . . . . . . . . . .
16055   4.06   4.69   5.19   5.57    4.5   4.88   5.17   5.37    4.6   4.78    4.9   4.97 . . . . . . . . . . . . . . . . . . .
16056   1.25   1.38   1.57   1.75    1.5   1.62   1.79   1.96    1.9   1.97    2.1   2.25 . . . . . . . . . . . . . . . . . . .
16057  -2.25  -2.44  -2.32  -2.07  -1.46  -1.21   -.79   -.33    .93   1.22   1.62   2.06 . . . . . . . . . . . . . . . . . . .
16058   2.44   2.87   3.19   3.38   2.33   2.46   2.46   2.37   1.07   1.03    .95    .87 . . . . . . . . . . . . . . . . . . .
16061   -.94  -1.31  -1.69  -2.19  -1.83   -2.2  -2.55  -2.96  -3.28  -3.55  -3.77     -4 . . . . . . . . . . . . . . . . . . .
16062 -10.06 -11.94 -13.31 -14.25 -11.71 -12.92 -13.83 -14.46 -12.62 -13.15 -13.47  -13.6 . . . . . . . . . . . . . . . . . . .
16063  10.13   11.5  11.94  11.94  10.17  10.79  10.83  10.67   8.95    9.4   9.54   9.58 . . . . . . . . . . . . . . . . . . .
16065   5.87    6.5   6.81   6.81   5.87   6.25   6.46   6.46   6.15   6.42   6.58   6.59 . . . . . . . . . . . . . . . . . . .
16068  -4.13  -4.81  -5.19   -5.5  -4.83  -5.46  -5.83  -6.17  -6.47  -7.05  -7.47  -7.84 . . . . . . . . . . . . . . . . . . .
16069   -.18   -.44   -.81  -1.31  -1.29  -1.75  -2.25  -2.75  -3.38  -3.72  -4.08  -4.43 . . . . . . . . . . . . . . . . . . .
16070      1   1.44   1.94   2.56   2.12   2.54   2.96   3.33      3   3.17   3.35   3.53 . . . . . . . . . . . . . . . . . . .
16072 -11.82 -13.12 -13.75 -13.62 -11.66 -12.54 -13.05 -13.12 -12.42 -13.12 -13.57 -13.76 . . . . . . . . . . . . . . . . . . .
16075    .69     .5    .06   -.44   -.13   -.33   -.66     -1   -.93  -1.15  -1.45  -1.74 . . . . . . . . . . . . . . . . . . .
16076  12.75  14.43  15.62  15.94  13.46   14.5  15.25  15.46  13.77  14.35  14.74  14.82 . . . . . . . . . . . . . . . . . . .
16077   1.44   1.81   2.26   2.75   2.29   2.62      3   3.46   3.61   3.88   4.15   4.45 . . . . . . . . . . . . . . . . . . .
16078      0   -.06   -.13   -.13   -.08   -.08   -.08   -.09   -.06   -.06   -.04   -.05 . . . . . . . . . . . . . . . . . . .
16079  20.12  22.44  23.56  23.57  20.54  21.83  22.33  22.13  19.73  20.35  20.52   20.3 . . . . . . . . . . . . . . . . . . .
16082    .82   1.12   1.44   1.93   1.79   2.17    2.5   2.92   3.07   3.28   3.45   3.65 . . . . . . . . . . . . . . . . . . .
16083   5.68   6.69   7.56   8.13   6.79   7.54   8.16   8.58   7.78   8.23   8.58    8.8 . . . . . . . . . . . . . . . . . . .
16084   -.81   -.68   -.37    .18    .21    .54    .96   1.58   2.43   2.77   3.15   3.58 . . . . . . . . . . . . . . . . . . .
16085  -4.12  -4.63  -4.75  -4.62  -3.58  -3.58  -3.33  -2.91  -1.03   -.77   -.43   -.03 . . . . . . . . . . . . . . . . . . .
16086  -1.69  -2.25  -2.94  -3.62     -3  -3.58  -4.17  -4.76  -4.85  -5.26  -5.65  -6.02 . . . . . . . . . . . . . . . . . . .
16090  -1.06   -1.5  -1.94  -2.26  -1.88  -2.17  -2.45  -2.66  -2.43  -2.59  -2.78   -2.9 . . . . . . . . . . . . . . . . . . .
16091   1.75   2.19   2.63      3   2.29   2.54   2.79      3   2.48   2.59    2.7   2.75 . . . . . . . . . . . . . . . . . . .
16092   5.87   6.93   7.75   8.13   6.75   7.46      8   8.25   7.35   7.75   8.03    8.1 . . . . . . . . . . . . . . . . . . .
16093  -5.56  -6.81  -7.88  -8.75  -7.29  -8.33  -9.25 -10.04 -10.05 -10.82  -11.5 -12.08 . . . . . . . . . . . . . . . . . . .
16096  -5.25  -6.43   -7.5  -8.31  -6.75  -7.59  -8.38  -8.96  -8.17  -8.65  -9.05  -9.32 . . . . . . . . . . . . . . . . . . .
16097   5.69   6.68    7.5   7.81   6.21   6.71   7.13   7.21   5.97   6.23   6.43   6.45 . . . . . . . . . . . . . . . . . . .
16098    -16 -18.12 -19.37 -19.62 -16.62 -17.75 -18.25 -18.09 -15.03 -15.43 -15.48 -15.15 . . . . . . . . . . . . . . . . . . .
16099  -3.25  -3.69  -3.88  -3.82     -3     -3  -2.88  -2.62  -1.37  -1.22  -1.03   -.78 . . . . . . . . . . . . . . . . . . .
16100   4.06   4.69   5.19   5.38   4.66   5.17   5.54   5.71    5.4    5.7   5.91   5.97 . . . . . . . . . . . . . . . . . . .
16103   1.25   1.19   1.12   1.06    .88    .79    .75     .7    .48     .4    .35    .28 . . . . . . . . . . . . . . . . . . .
16104   6.87   7.87   8.44   8.75   7.33   7.83   8.09   8.21   7.12    7.4    7.5   7.53 . . . . . . . . . . . . . . . . . . .
16105      0   -.12   -.31   -.56   -.58   -.83  -1.13  -1.41  -1.65  -1.83  -2.01  -2.18 . . . . . . . . . . . . . . . . . . .
16106  -7.93  -9.13  -9.94 -10.32  -8.71  -9.42  -9.87 -10.04   -8.8  -9.15  -9.32  -9.32 . . . . . . . . . . . . . . . . . . .
16107  10.12  11.56   12.5  12.75  10.71  11.55  12.08  12.16  10.48  10.88   11.1  11.07 . . . . . . . . . . . . . . . . . . .
16110   2.25   2.88   3.44   3.94   3.16   3.54   3.88   4.13   3.44   3.59    3.7   3.77 . . . . . . . . . . . . . . . . . . .
16111  -4.94  -5.63     -6     -6  -4.87  -5.17   -5.3  -5.17  -4.07  -4.19  -4.22  -4.12 . . . . . . . . . . . . . . . . . . .
16112  12.01  13.44  14.06  14.06  12.12  12.96  13.25  13.13  11.33  11.67  11.69  11.47 . . . . . . . . . . . . . . . . . . .
16113     -1  -1.31  -1.62  -1.87  -1.54  -1.79  -2.04  -2.26  -2.28  -2.47  -2.67  -2.87 . . . . . . . . . . . . . . . . . . .
16114   4.74   5.62   6.37   6.75   5.42   5.79      6   6.01   4.58   4.67   4.73   4.68 . . . . . . . . . . . . . . . . . . .
16118    .04   -.12   -.31    -.5   -.44   -.54   -.62   -.71   -.62   -.65   -.68   -.68 . . . . . . . . . . . . . . . . . . .
16119   -.04   -.13   -.19   -.19   -.06   -.04    .04    .12    .44     .5    .57    .65 . . . . . . . . . . . . . . . . . . .
16120    .63    .88   1.13   1.38   1.08   1.24   1.37    1.5   1.17   1.22   1.28   1.32 . . . . . . . . . . . . . . . . . . .
16121  -5.03  -5.75  -6.19  -6.38  -5.35  -5.83  -6.12  -6.25  -5.61  -5.89  -6.07  -6.14 . . . . . . . . . . . . . . . . . . .
16124   4.62   5.18   5.44   5.38   4.54   4.79   4.87   4.79   4.03   4.15   4.17    4.1 . . . . . . . . . . . . . . . . . . .
16125   2.44   2.82   3.18   3.44   2.75   3.05   3.29    3.5    3.3   3.52   3.72   3.87 . . . . . . . . . . . . . . . . . . .
16126   2.56      3   3.32    3.5   2.71   2.91   3.09   3.13    2.3   2.35   2.38   2.35 . . . . . . . . . . . . . . . . . . .
16127  -2.12   -2.5  -2.82     -3  -2.42  -2.71  -2.96  -3.13  -2.98  -3.17  -3.35  -3.48 . . . . . . . . . . . . . . . . . . .
16128   2.81    3.5   4.26   4.87   3.88   4.42   4.96   5.42   4.93   5.25   5.57   5.83 . . . . . . . . . . . . . . . . . . .
16131  -1.56  -1.63  -1.69  -1.63  -1.21  -1.25  -1.29  -1.25   -.93   -.93   -.92   -.85 . . . . . . . . . . . . . . . . . . .
16132  -4.97  -5.87  -6.44  -6.74  -5.82  -6.37  -6.71  -6.88  -6.18   -6.5  -6.67  -6.75 . . . . . . . . . . . . . . . . . . .
16133   -.75   -.88   -.94  -1.01   -.83   -.92   -.96   -.99    -.9   -.95   -.98     -1 . . . . . . . . . . . . . . . . . . .
16134   2.44      3   3.44   3.75   3.13    3.5   3.79   3.95   3.42    3.6    3.7   3.72 . . . . . . . . . . . . . . . . . . .
16135  19.78  22.94  25.38  26.88  22.02     24  25.54  26.42  22.96     24  24.77  25.16 . . . . . . . . . . . . . . . . . . .
16138    4.5   5.62   6.75   7.69   6.33   7.21   8.05   8.75   8.05   8.47   8.86   9.12 . . . . . . . . . . . . . . . . . . .
16139   1.81   2.76   3.75   4.93   3.96   4.75   5.54   6.46   6.38   6.81   7.22    7.7 . . . . . . . . . . . . . . . . . . .
16140  -1.56  -1.57  -1.38     -1   -.83   -.71   -.46   -.13    .27    .34    .47    .65 . . . . . . . . . . . . . . . . . . .
16141    .31    .32    .25    .13      0   -.13   -.29   -.46   -.95  -1.07  -1.19  -1.35 . . . . . . . . . . . . . . . . . . .
16142   -.53   -.88  -1.25  -1.62  -1.19  -1.41  -1.67  -1.91  -1.71  -1.85     -2  -2.18 . . . . . . . . . . . . . . . . . . .
16145  -1.69  -1.94  -2.25  -2.43  -1.88     -2  -2.17  -2.29  -1.78   -1.8  -1.84  -1.87 . . . . . . . . . . . . . . . . . . .
16146   7.38    8.5   9.43  10.12   8.75   9.62  10.38  10.96  10.65   11.2  11.65     12 . . . . . . . . . . . . . . . . . . .
16147   -.44   -.37   -.25   -.06    .04    .13    .25    .42    .51    .49    .52    .55 . . . . . . . . . . . . . . . . . . .
16148  -4.19  -5.13  -5.87   -6.5  -5.37  -6.04  -6.59     -7  -6.53  -6.92  -7.25  -7.47 . . . . . . . . . . . . . . . . . . .
16149  -1.37     -2  -2.69  -3.44  -2.83  -3.42     -4  -4.59  -4.63     -5  -5.38  -5.76 . . . . . . . . . . . . . . . . . . .
16152   5.56   6.57   7.31   7.75   6.37   7.04   7.55   7.84   7.13   7.52   7.83   8.01 . . . . . . . . . . . . . . . . . . .
16153    .94   1.18   1.44   1.69   1.29    1.5    1.7   1.91   1.85   1.98    2.1   2.22 . . . . . . . . . . . . . . . . . . .
16154   1.18   1.32    1.5   1.63   1.25   1.29   1.34   1.29    .75     .7    .68    .62 . . . . . . . . . . . . . . . . . . .
16155   -.75   -.94  -1.13  -1.19     -1   -1.2  -1.46  -1.62  -1.87  -2.05  -2.23  -2.34 . . . . . . . . . . . . . . . . . . .
16156  -8.81 -10.38 -11.56 -12.32 -10.21 -11.25 -12.04 -12.58 -11.58 -12.22  -12.7    -13 . . . . . . . . . . . . . . . . . . .
16159  -4.12  -4.81  -5.43  -5.93  -5.16  -5.75  -6.25  -6.63   -6.4  -6.78   -7.1  -7.36 . . . . . . . . . . . . . . . . . . .
16160  -1.07  -1.06  -1.01   -.82   -.88   -.96     -1   -.96  -1.32  -1.45  -1.52  -1.54 . . . . . . . . . . . . . . . . . . .
16161   5.76   6.81   7.63      8   6.71   7.46   8.04   8.34    7.6   8.05   8.37   8.52 . . . . . . . . . . . . . . . . . . .
16162  -6.19  -7.13  -7.75  -8.06  -6.87  -7.46  -7.83  -8.05   -7.2  -7.47   -7.6  -7.62 . . . . . . . . . . . . . . . . . . .
16163 -26.69 -29.93 -32.13 -33.18  -28.8 -30.96 -32.42 -33.08 -30.33 -31.63 -32.48 -32.86 . . . . . . . . . . . . . . . . . . .
16166  -3.94  -5.32  -6.81  -8.44  -6.91  -8.08  -9.21 -10.29   -9.2  -9.62  -9.99 -10.32 . . . . . . . . . . . . . . . . . . .
16167   5.13   5.57   5.82   5.94   5.37   5.75      6   6.12   5.83   6.05   6.19   6.27 . . . . . . . . . . . . . . . . . . .
16168    .37    .31    .37    .43    .59    .71    .83    .92   1.27   1.37   1.48   1.53 . . . . . . . . . . . . . . . . . . .
16169    .25    .13   -.12   -.44   -.21   -.42   -.66   -.96  -1.05   -1.2  -1.37  -1.55 . . . . . . . . . . . . . . . . . . .
16173  -2.62  -3.26  -3.82  -4.37  -3.67  -4.13  -4.54  -4.92  -4.55   -4.8  -5.03  -5.22 . . . . . . . . . . . . . . . . . . .
16174  -9.94 -11.43  -12.5 -13.13 -11.29 -12.29    -13 -13.37 -12.02  -12.5 -12.78 -12.83 . . . . . . . . . . . . . . . . . . .
16175 -11.31 -11.69  -11.5 -11.06 -10.21 -10.16   -9.8  -9.29  -7.88  -7.67   -7.3  -6.88 . . . . . . . . . . . . . . . . . . .
16176    .31   -.13   -.62  -1.06   -.63  -1.05  -1.45  -1.84  -1.78  -2.03  -2.27  -2.47 . . . . . . . . . . . . . . . . . . .
16177   8.37   8.88      9   8.81   7.88   8.17   8.25   8.17   7.83   8.08   8.17   8.18 . . . . . . . . . . . . . . . . . . .
16180  -4.68  -5.31  -5.69  -5.75  -4.79  -5.12   -5.3  -5.21  -4.22  -4.36  -4.39  -4.36 . . . . . . . . . . . . . . . . . . .
16181     -5  -5.94  -6.62  -7.12  -5.88  -6.42  -6.75  -6.95  -5.76  -5.94  -6.05   -6.1 . . . . . . . . . . . . . . . . . . .
16182  -7.56  -7.62  -7.19  -6.38  -5.95  -5.75   -5.2  -4.42  -3.37  -3.13  -2.73  -2.19 . . . . . . . . . . . . . . . . . . .
16183   5.56   6.43   7.06   7.57   6.41   7.04   7.45   7.75   6.98   7.27   7.47    7.6 . . . . . . . . . . . . . . . . . . .
16184 -13.19 -14.68 -15.31 -15.19 -12.91 -13.46  -13.5 -13.17 -11.08 -11.32 -11.29 -11.05 . . . . . . . . . . . . . . . . . . .
end
format %tdnn/dd/CCYY Date

From the do-file:

*Eurodollar Futures Analysis


clear // clear all
version 15 // uses Stata version 15
set more off // runs commands at one go
capture log close //closes any preexisting logs
log using eurodollarprojectlog , replace // creates new log


import excel "C:\stata\fedhistoricaldata.xls", firstrow

//Should I use gen double Date1 = clock(Date, "DMY")?
save fedhistoricaldata , replace
clear


import excel "C:\stata\bundlefutureshistoricalpricing.xlsx", firstrow  
save  "C:\stata\bundlefutureshistoricalpricing.dta", replace





merge 1:1 Date using fedhistoricaldata

The error message is: variable Date does not uniquely identify observations in the master data

[Linus Tham]

Hi William,

I ran the suggested code and the system returned about 13 missing values listed as duplicates. Those I removed using the drop command. Unfortunately, the problem seems to be still there unfortunately.


. isid Date
variable Date should never be missing
r(459);

.
. duplicates list Date

Duplicates in terms of Date

+-------------+
| obs: Date |
|-------------|
| 2685 . |
| 2686 . |
| 2687 . |
| 2688 . |
| 2689 . |
|-------------|
| 2690 . |
| 2691 . |
| 2692 . |
| 2693 . |
| 2694 . |
|-------------|
| 2695 . |
| 2696 . |
| 2697 . |
| 2698 . |
+-------------+

.
. count if missing(Date)
14

.

  
 *Eurodollar Futures Analysis   clear // clear all version 15 // uses Stata version 15 set more off // runs commands at one go capture log close //closes any preexisting logs log using eurodollarprojectlog , replace // creates new log   import excel "C:\stata\fedhistoricaldata.xls", firstrow  //Should I use gen double Date1 = clock(Date, "DMY")? save fedhistoricaldata , replace clear   import excel "C:\stata\bundlefutureshistoricalpricing.xlsx", firstrow   save  "C:\stata\bundlefutureshistoricalpricing.dta", replace   drop if Date == . // deletes missing variables   merge 1:1 Date using fedhistoricaldata

[William Lisowski]

Then after dropping the observations missing the Date, run the commands again to identify further problems.

[William Lisowski]

A subsequent thought. You tell us "the problem still seems to be there" but you don't tell us what Stata told you. I'd guess it now told you "Date does not uniquely identify observations in the using data" rather than "the master data" which tells us fedhistoricaldata.xls also has problems.

Section 12.1 of the Statalist FAQ is particularly pertinent

12.1 What to say about your commands and your problem

Say exactly what you typed and exactly what Stata typed (or did) in response. N.B. exactly!
...
Never say just that something "doesn't work" or "didn't work", but explain precisely in what sense you didn't get what you wanted.