Merging timeseries data with panel data

[Lisa Wilson]

Dear all
I have a GDP time series data that is identified by year
I have another dataset that is a panel of firms and years. In each year there could be more than 500 firms

I want to merge the two dataset so that the each firm (observation) in the same year have the same year's GDP variable in the merged data.

The panel is sorted by firm and yr and the time series is sorted by yr. I do not know how to create as many GDP variables to be attached to all firms in the panel of the same year.

Hope someone can help in this basic question.

[cherry singhal]

Could you attach a small subset of both datasets as example. ".dta" format would works best.

[Rich Goldstein]

please do not attach as ".dta" format - some of us will not look at binary files from strangers; please use -dataex- which you can download from ssc (ssc install dataex); note that this is discussed in the FAQ which you should (both) read

[Lisa Wilson]

Thanks a lot
Here you go:

This is the time series data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int yr double(A B C D E F G H I)
1993                   .                  .                  .                     .                  .                     .                   .                    .                    .
1994                   .                  .                  .                     .                  .                     .                   .                    .                    .
1995                   .                  .                  .                     .                  .                     .                   .                    .                    .
1996                   .                  .                  .                     .                  .                     .                   .                    .                    .
1997  .07686649262905121 .41408050060272217 .06807927787303925   .015561746433377266 .08908393234014511    .03162401169538498 -.04080241918563843   .07818510383367539  .011860157363116741
1998  .05942625552415848  .4637705683708191  .1478828638792038 -.0027880482375621796 .09010057896375656   -.08675749599933624 -.16662584245204926     .061351228505373   .02986784465610981
1999 .051245611160993576  .5529971122741699 .11257541924715042  -.004909822251647711 .11913780868053436   -.17716869711875916 -.14268964529037476  .022304821759462357   .04091258719563484
2000 .059583112597465515  .5565321445465088 .15415193140506744   -.03733557462692261 .11856716871261597   -.07886789739131928 -.10069658607244492  .039455726742744446  .036498989909887314
2001 .056881554424762726  .4992966055870056 .19092656672000885  -.030559087172150612    .13016277551651   -.04180307313799858 -.10558260977268219    .0420086495578289   .04841108247637749
2002  .06602281332015991 .49288809299468994 .19250889122486115   -.02527182549238205 .13590385019779205   -.02206229791045189 -.07041110098361969   .03046783059835434   .04948658496141434
2003  .10015379637479782  .5495551824569702 .14697235822677612   -.04032524302601814 .15703880786895752    .07714150846004486   .0916282907128334  .030940188094973564   .04704192280769348
2004  .11272762715816498 .47492387890815735  .1505681425333023   -.03398393839597702 .11744534224271774      .164469376206398  .17766645550727844   .07197792828083038  .044265858829021454
2005  .11656798422336578 .49511027336120605 .18445873260498047     -.016984848305583 .12646949291229248    .02056104689836502   .0723964124917984   .07372573763132095  .015031903982162476
2006   .1143796518445015  .4909249544143677 .17532528936862946    -.0226055309176445 .10448917001485825   .011452791281044483  .01773502677679062   .06631592661142349  .021084487438201904
2007    .082980215549469   .481174111366272 .12175806611776352  -.020789828151464462 .08703602105379105    .07199312746524811  .15104155242443085   .05407840758562088  .034208059310913086
2008 .018232189118862152  .3765600025653839 .14375147223472595  -.011520056053996086 .07795392721891403 .00031906701042316854  .06812912225723267   .06491019576787949 -.001983343157917261
2009  .05049818009138107 .33068883419036865  .3072248101234436 -.0060830325819551945 .08502073585987091    .07928105443716049 -.06647714972496033  .019296811893582344  .005150509532541037
2010 .044236671179533005   .329570472240448 .27922987937927246 -.0006379358237609267 .07222646474838257      .128484845161438 .004222649149596691 -.001841394929215312   .03957720100879669
2011 .020993269979953766  .2821280360221863 .30585920810699463   .013252580538392067 .08148684352636337    .18837089836597443 .031448062509298325  -.02147984690964222  .017678722739219666
2012 .041742485016584396  .3620907962322235  .3696534037590027   .006065878085792065 .09150177240371704    .08426559716463089 -.12247397750616074  .004965717904269695    .0105704041197896
2013  .08132094144821167  .4666249454021454 .34186074137687683    .00469528092071414 .10991688072681427   .018460262566804886 -.08677686750888825    .0224554855376482 .0074303774163126945
end

Here is the panel data

* Example generated by -dataex-. To install: ssc install dataex
clear
input int yr long permno float AR1 double(oiadp sale)
1992 10001   .10706402   1.922    22.95
1993 10001   .11046952   2.741   27.629
1994 10001  -.12216048   2.636    27.68
1995 10001 .0012711287   3.092   30.548
1996 10001   -.1233126   2.965   31.318
1997 10001  -.28641722   3.195   38.215
1998 10001   .17454442   3.752   43.064
1999 10001   -.5765607    3.24   53.461
2000 10001    .5265094    3.14   72.196
2001 10001  -.14030312   6.002   119.94
2002 10001  -.05196172   3.322   99.635
2003 10001   -.9405031   1.179   79.146
2004 10001  -.08924145   1.188   73.291
2005 10001   .32064044   4.366   76.709
2006 10001   .27433574   5.267   84.278
2007 10001   .11996178   5.412   59.373
2008 10001    .3607194   7.291   88.469
2009 10001  -.54204905   9.063   71.454
2010 10001 -.004650235   11.08     91.5
2011 10001   .06100617  10.242   99.217
2012 10001  -.20000896   9.601   93.816
2013 10001   -.1985588  12.435  118.835
2014 10001   .14989147   7.162   132.57
2005 10002  -.11656278  28.113   83.892
2007 10002   -.3279001  18.484  118.646
1992 10010   -.3832816   2.639   54.998
1993 10010   -.1641841   5.734    69.56
1994 10010   -.5155967   8.742   68.097
1992 10011   -.8686556   -.262   10.578
1993 10011 -.005852818    .727   16.539
1994 10011   1.1549748   3.227   26.664
1995 10011    .6295013   5.625   36.272
1996 10011   -.4368209   5.796   44.965
1997 10011   -.3692414  11.711  113.855
1993 10012   .51119745   -.127    7.321
1994 10012  -.37088925     .85   11.205
1995 10012   .11773665   -.594   11.495
1996 10012    1.692716   1.773   18.006
1997 10012    -.742703   -.988   14.024
1998 10012  -.21028525  -1.911   13.464
1999 10012  -.12728977  -2.402   12.604
2001 10012   -.6322526   1.746   35.823
2002 10012    1.200684   1.919   30.505
2003 10012  -.56796163    .906   34.489
1991 10016    .1552558  87.522 1243.486
1992 10016  -.24025673  75.726 1172.797
1993 10016   -.6198083 -70.961  768.642
1994 10016  -.09719847 -14.818   319.42
1996 10016  -.18109325  40.028  385.717
1998 10016    .3749625  29.429  177.108
1999 10016   -.4587374  37.565  201.541
2000 10016    .3782166  43.722  263.981
1992 10018    .4281936    .522    8.469
1992 10019  -.42742145   2.162   39.709
1993 10019      .20989   1.851   60.791
1994 10019    .5265601   2.001   65.073
1995 10019  -.29347476   3.682   75.994
1996 10019  .021676026   8.316   89.997
1997 10019    .8799776  10.907  103.517
1998 10019   -.6581886  15.639  148.069
1991 10021   -.6056243    .169    1.731
1993 10021    -.533831   -.002    2.097
1992 10024  -.18895994   -.502     .417
1992 10025   -.2043822    6.66  142.621
1993 10025    1.210577  14.277  153.307
1994 10025   .15373424  19.734  184.669
1995 10025   -.1779593  25.224  242.886
1996 10025    .8382844  15.538  270.534
1997 10025   -.6165832  39.852  759.123
1998 10025  -.14736277  37.695  701.239
1999 10025   -.2800684  39.041  670.052
2000 10025    .6309323  22.831  701.321
2001 10025   -.4653198  25.725    639.7
2002 10025  -.58532536  18.859  660.578
2003 10025   -.6512212   19.67  759.473
2004 10025    .7758634  36.373  810.982
2005 10025   .42898685  44.719  732.724
2006 10025   .21321124  69.378  802.109
2007 10025  -.15066263  56.928  786.015
2008 10025  -.09733468  11.535  762.231
2009 10025   -.1576767   60.38  744.819
2010 10025  -.07373081  15.583   800.57
2011 10025   .18933563  25.947  974.792
2012 10025     .939958  56.526 1152.535
2013 10025   -.7246499  33.431 1143.852
2014 10025   .58184576   7.994  1192.99
1992 10026  -.12938222   8.007  126.927
1993 10026    .2799975  12.207   147.19
1994 10026   -.4082032  12.403  174.425
1995 10026   -.1771983   7.008  185.362
1996 10026  -.00555718   7.948  186.018
1997 10026    .1811713   11.64  220.318
1998 10026   .15146422  20.461   262.39
1999 10026  -.55749077  24.963  288.439
2000 10026   .05194317  18.812  321.112
2001 10026    .9946417  20.549  351.696
2002 10026  -.02564992  28.266  353.187
2003 10026   -.3981098  30.847  364.567
2004 10026 -.027994163  35.192  416.588
2005 10026   .20858233  40.249  457.112
end

[cherry singhal]

please do not attach as ".dta" format - some of us will not look at binary files from strangers; please use -dataex- which you can download from ssc (ssc install dataex); note that this is discussed in the FAQ which you should (both) read

Thanks for correcting. I was not aware of that.

[Rich Goldstein]

assume that the first data set above is called junk1 and the second junk2 and that junk2 is in memory:

sort yr permno
save junk2, replace
us junk1
sort yr
merge 1:m yr using junk2

I think that is what you are looking for but the question is not completely clear to me so please let us know if I have misunderstood

[Lisa Wilson]

Hi
I tried the code. But no matching occurs!!
Here is the code:
Foranalysis.dta is the panel data set
Junk2 that represents only time series data in memory, and then

rename yr timeid
rename _cons constant
sort timeid
save junk2, replace
use Foranalysis.dta,clear
sort timeid permno
merge 1:m timeid using junk2

What happens is :

 merge 1:m timeid using junk2
variable timeid does not uniquely identify observations in the master data

if I change the last code to:

. merge m:1 timeid using junk2

    Result                           # of obs.
    -----------------------------------------
    not matched                        82,846
        from master                    82,825  (_merge==1)
        from using                         21  (_merge==2)

    matched                                 0  (_merge==3)

Can anyone help please?

Thanks
Lisa