Merge with complex requirements for date

[Edward Wu]

Hey!

I need to merge two files, each with 3 variables.

The first one has date, city, and score. The score is a city may or may not have on any day, including Saturdays, Sundays and holidays.

The second one has date, city and ret. ret is the return of a city's stocks, which is available on all business days, not available on Saturdays, Sundays and holidays.

I need to merge by date and city.

The key point is, I need to merge the ret with the previous period day(s)' score.

For example, I want to match Wednesday's ret with Tuesday's score, Tuesday's ret with Monday's score

However, for Monday, I will need to match the ret with Friday, Saturday and Sunday's score (if the score are available on those days, need to add them as a single score).

And if Monday is also a holiday, which means there is no ret available on that day, I will need to match Tuesday's ret with Friday, Saturday, Sunday and Monday's score (the score may or may not be available on any of those days, if they are available, I need to add the available ones as a single score).

If Wednesday is a holiday, I will need to match Thursday's ret with Tuesday and Wednesday's score.

Anyone can help? Great thanks!

Here are the data examples:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 city float date double score
"Atlanta"       17992  2
"Philadelphia"  17992 -2
"Baltimore"     17993  2
"Chicago"       17993  2
"Cincinnati"    17993 -2
"Cleveland"     17993 -2
"Dallas"        17993  2
"Denver"        17993 -2
"Detroit"       17993 -2
"Houston"       17993 -2
"Los Angeles"   17993  4
"Miami"         17993  2
"Minneapolis"   17993 -2
"New York"      17993  0
"Phoenix"       17993  2
"Pittsburgh"    17993  2
"San Diego"     17993 -2
"San Francisco" 17993 -2
"Seattle"       17993  2
"St. Louis"     17993 -2
"Toronto"       17993  2
"Washington"    17993 -2
"Atlanta"       17994  2
"Boston"        17994  2
"Chicago"       17994  0
"Denver"        17994  2
"Detroit"       17994 -2
"Houston"       17994  2
"Kansas"        17994 -2
"Los Angeles"   17994 -4
"Miami"         17994  2
"Milwaukee"     17994 -2
"Minneapolis"   17994  2
"Philadelphia"  17994 -2
"Phoenix"       17994 -2
"Pittsburgh"    17994 -2
"San Diego"     17994  2
"San Francisco" 17994  4
"Seattle"       17994 -2
"St. Louis"     17994  2
"Tampa"         17994 -2
"Toronto"       17994  2
"Washington"    17994 -2
"Atlanta"       17995 -2
"Baltimore"     17995  2
"Boston"        17995 -2
"Chicago"       17995  0
"Cincinnati"    17995 -2
"Cleveland"     17995 -2
"Dallas"        17995  2
"Denver"        17995  2
"Detroit"       17995  2
"Houston"       17995 -2
"Kansas"        17995  2
"Los Angeles"   17995  0
"Miami"         17995  2
"Milwaukee"     17995  2
"Minneapolis"   17995  2
"New York"      17995  0
"Philadelphia"  17995  2
"Phoenix"       17995 -2
"Pittsburgh"    17995  2
"San Diego"     17995 -2
"San Francisco" 17995  0
"Seattle"       17995 -2
"St. Louis"     17995 -2
"Tampa"         17995  2
"Toronto"       17995 -2
"Washington"    17995 -2
"Baltimore"     17996 -2
"Boston"        17996 -2
"Chicago"       17996 -2
"Cincinnati"    17996  2
"Cleveland"     17996 -2
"Dallas"        17996  2
"Detroit"       17996 -2
"Kansas"        17996  2
"Los Angeles"   17996 -2
"Milwaukee"     17996 -2
"Minneapolis"   17996 -2
"New York"      17996  0
"Pittsburgh"    17996 -2
"San Diego"     17996  2
"San Francisco" 17996  2
"Seattle"       17996  2
"St. Louis"     17996  2
"Tampa"         17996  2
"Toronto"       17996  2
"Atlanta"       17997  2
"Baltimore"     17997  2
"Boston"        17997 -2
"Chicago"       17997 -4
"Cleveland"     17997 -2
"Dallas"        17997 -2
"Denver"        17997  2
"Detroit"       17997  2
"Houston"       17997 -2
"Kansas"        17997 -2
"Los Angeles"   17997  0
"Miami"         17997  2
end
format %td date

* Example generated by -dataex-. To install: ssc install dataex
clear
input long date str100 city double ret
17993 " San Jose"            -.04182514548301697
17993 "Abbott Park"         -.017225710675120354
17993 "Abilene"             -.028944695368409157
17993 "Abingdon"             .009329911321401596
17993 "Acton"               -.010382187319919467
17993 "Ada"                   .09270834177732468
17993 "Addison"              -.01720428466796875
17993 "Adelaide"            -.003273989073932171
17993 "Admiralty"            -.03225809708237648
17993 "Agoura Hills"        -.031331565231084824
17993 "Akron"                -.00909065525047481
17993 "Alameda"             -.017110730210940044
17993 "Albany"              -.006818457196156184
17993 "Albuquerque"        -.0017507798038423061
17993 "Alexandria"           -.02164800837635994
17993 "Algonquin"           -.005625009536743164
17993 "Alhambra"             .012499988079071045
17993 "Aliso Viejo"          -.02459175552108458
17993 "Allen"                -.02682736050337553
17993 "Allentown"           -.024882686013976734
17993 "Alma"                  .23333334922790527
17993 "Alpena"                -.1931818425655365
17993 "Alpharetta"          -.018421345204114915
17993 "Amarillo"           -.0038373940624296665
17993 "Ames"                 .011280998587608337
17993 "Amherst"             .0062499940395355225
17993 "Amityville"            .06509446911513805
17993 "Amsterdam"           -.027492067689308897
17993 "Anaheim"             -.057753642275929454
17993 "Anchorage"             -.0246212063357234
17993 "Andover"              .004906466230750084
17993 "Anjo"                 -.01697019301354885
17993 "Ankeny"              -.012083482928574085
17993 "Ann Arbor"            -.01901837616848449
17993 "Annapolis"             .04736168533563614
17993 "Annapolis Junction"                     0
17993 "Arden Hills"         .0031948850955814123
17993 "Ardsley"              -.04266125708818436
17993 "Arlington"           -.025484277789170545
17993 "Armonk"              -.006456697825342417
17993 "Armstrong"           -.012919885106384754
17993 "Asheville"            -.05326877161860466
17993 "Atchison"              .09090912342071533
17993 "Athens"               -.02333270153030753
17993 "Athol"                .023004069924354553
17993 "Atlanta"             -.012982352770416549
17993 "Aubervilliers"         .00886528193950653
17993 "Auburn"              -.020947135984897614
17993 "Auburn Hills"         .010497731156647205
17993 "Auckland"            .0014245340134948492
17993 "Aurora"               -.04691092297434807
17993 "Austin"              -.023200941279355902
17993 "Aventura"             .016666650655679405
17993 "Avon Lake"             .01111110020428896
17993 "Ayer"                -.030643533915281296
17993 "Azusa"               -.058823537081480026
17993 "Bad Homburg"         -.016341565176844597
17993 "Bakersfield"         -.018642673268914223
17993 "Bala Cynwyd"          .020930051803588867
17993 "Ball Ground"         -.045701757073402405
17993 "Baltimore"           .0013557171332649887
17993 "Bandung"              .007754899095743895
17993 "Bannockburn"         -.002610963536426425
17993 "Baoding"               .06932149827480316
17993 "Bar Harbor"         -.0011985891032963991
17993 "Barcelona"            -.06800003349781036
17993 "Basel"               -.015333954710513353
17993 "Basking Ridge"           .080808125436306
17993 "Bassett"                 .114457868039608
17993 "Batavia"               .06276144087314606
17993 "Batesville"           -.04352939873933792
17993 "Baton Rouge"           .02562946267426014
17993 "Battle Creek"         .013943768106400967
17993 "Bayonne"                .0128431161865592
17993 "Beachwood"           -.027322443202137947
17993 "Beaverton"            -.03733182675205171
17993 "Bedford"              -.01293545519001782
17993 "Bedford Heights"      -.03582720458507538
17993 "Bedminster"           -.02337666228413582
17993 "Beijing"               .01483103216492704
17993 "Bellevue"            -.017927246996098094
17993 "Bellingham"            .06711410731077194
17993 "Bellport"              -.1086956113576889
17993 "Belo Horizonte"      -.019999999552965164
17993 "Beloit"              -.030718566849827766
17993 "Beltsville"          -.013289024122059345
17993 "Bend"                 -.03846150264143944
17993 "Bengaluru"           -.019413191359490156
17993 "Bensalem"             -.05015496863052249
17993 "Bensenville"           .01683027856051922
17993 "Benton Harbor"        -.02861185185611248
17993 "Bentonville"         -.023011600831523538
17993 "Berkeley"            -.017511537531390786
17993 "Berkeley Heights"      .24167358875274658
17993 "Berlin"                .04716988280415535
17993 "Bern"                -.048848558217287064
17993 "Bernardsville"        .015873000025749207
17993 "Berwyn"               .003881467661509911
17993 "Bethesda"             .003181572330908643
17993 "Bethlehem"           -.037288181483745575
end
format %td date

[Edward Wu]

Someone can help? Thanks a lot.

[Edward Wu]

Still unsolved

[Jorrit Gosens]

Your provide an example of your data with dataex, and that is useful, but the question remains difficult to answer.
This code below already gets you a few steps closer.
It shows you how to merge and how to find lagged values of score.
I don't know what days are holidays, so I can say little about this.
Also note that your data examples, although they are in the right format, provide data that gives very few (one) matches, so this is a bit of a theoretical exercise.

merge 1:1 date city using dataset2.dta // use actual filenam
gen dayofweek = dow(date) // gives numerical values with 0=Sunday,...,6=Saturday
egen city_id = group(city)
xtset city_id date
bys city_id (date): gen match_ret_score = L1.score
bys city_id (date): replace match_ret_score = L3.score if dayofweek==1 // take value of score for friday if day is a monday

[Edward Wu]

Thanks! Your code is very useful.

For the holidays, I think it's simply the missing business days in the return dataset. For example, if there is no observation on a Tuesday for stock returns, then Tuesday must be a holiday.

I think the last step may not work:
bys city_id (date): replace match_ret_score = L3.score Because for Monday, I will need to match the ret with Friday, Saturday and Sunday's score (the score may or may not be available on any of those days, if they are available, I need to add the available ones as a single score). For example, if the score on Friday is +2, Saturday is +1, Sunday is 0. The matching score should be +3. And here are the example for NYC:

 * Example generated by -dataex-. To install: ssc install dataex clear input str14 city float date double score "New York" 17993   0 "New York" 17995   0 "New York" 17996   0 "New York" 17997   0 "New York" 17998   4 "New York" 17999  -4 "New York" 18000  -4 "New York" 18001   2 "New York" 18002   4 "New York" 18003  -4 "New York" 18004   4 "New York" 18005   0 "New York" 18006   0 "New York" 18008   0 "New York" 18009   0 "New York" 18010  -2 "New York" 18011   0 "New York" 18012   0 "New York" 18013  -4 "New York" 18014   0 "New York" 18015   0 "New York" 18016   0 "New York" 18017   2 "New York" 18018   4 "New York" 18019  -4 "New York" 18021   0 "New York" 18022   0 "New York" 18023   0 "New York" 18024   0 "New York" 18025   4 "New York" 18026   0 "New York" 18027   4 "New York" 18028  -2 "New York" 18029   0 "New York" 18030   0 "New York" 18031   4 "New York" 18032   4 "New York" 18033   4 "New York" 18034   0 "New York" 18035   0 "New York" 18036   0 "New York" 18037   0 "New York" 18038   2 "New York" 18039   0 "New York" 18040   4 "New York" 18041  -4 "New York" 18042   4 "New York" 18043   0 "New York" 18044   4 "New York" 18046   4 "New York" 18047   0 "New York" 18048   0 "New York" 18049   0 "New York" 18050   0 "New York" 18051  -2 "New York" 18052   0 "New York" 18053   2 "New York" 18054  -4 "New York" 18055   4 "New York" 18056   2 "New York" 18057   0 "New York" 18058  -4 "New York" 18059  -4 "New York" 18060   0 "New York" 18061   0 "New York" 18062   0 "New York" 18064   4 "New York" 18065  -4 "New York" 18066  -4 "New York" 18067   4 "New York" 18068  -4 "New York" 18069  -4 "New York" 18070   2 "New York" 18071  -4 "New York" 18072   4 "New York" 18073   4 "New York" 18074   0 "New York" 18075   0 "New York" 18076   0 "New York" 18077  -2 "New York" 18078   0 "New York" 18079   4 "New York" 18080   0 "New York" 18081   0 "New York" 18082   0 "New York" 18083   0 "New York" 18084  -2 "New York" 18085   0 "New York" 18086   4 "New York" 18087   0 "New York" 18088  -4 "New York" 18089   0 "New York" 18090   0 "New York" 18094  -2 "New York" 18095   0 "New York" 18096   4 "New York" 18097   0 "New York" 18098   4 "New York" 18099   0 "New York" 18100   0 "New York" 18101   2 "New York" 18102   0 "New York" 18103   0 "New York" 18104   4 "New York" 18105   4 "New York" 18106   0 "New York" 18107   2 "New York" 18108  -2 "New York" 18109  -4 "New York" 18110   0 "New York" 18111   0 "New York" 18112  -2 "New York" 18113   0 "New York" 18114   4 "New York" 18115   0 "New York" 18116   0 "New York" 18117   0 "New York" 18118   4 "New York" 18119  -4 "New York" 18120   0 "New York" 18121   4 "New York" 18122   2 "New York" 18123   4 "New York" 18124   0 "New York" 18125   0 "New York" 18126  -4 "New York" 18127   4 "New York" 18128   0 "New York" 18129  -2 "New York" 18130   4 "New York" 18131  -4 "New York" 18132   0 "New York" 18133  -2 "New York" 18134  -4 "New York" 18135   0 "New York" 18136   0 "New York" 18137   0 "New York" 18138   0 "New York" 18139   4 "New York" 18140   2 "New York" 18141   0 "New York" 18142   0 "New York" 18143   4 "New York" 18144   0 "New York" 18145   0 "New York" 18146   0 "New York" 18147   4 "New York" 18148   0 "New York" 18149   0 "New York" 18150  -2 "New York" 18151  -4 "New York" 18152   0 "New York" 18153   8 "New York" 18154   2 "New York" 18155  -4 "New York" 18156   0 "New York" 18157  -2 "New York" 18158  -4 "New York" 18159   4 "New York" 18160  10 "New York" 18161  -4 "New York" 18162   0 "New York" 18163   0 "New York" 18165   4 "New York" 18166   0 "New York" 18167  14 "New York" 18168   0 "New York" 18169   0 "New York" 18170  -4 "New York" 18172  -1 "New York" 18173  -1 "New York" 18174   4 "New York" 18175   0 "New York" 18177   4 "New York" 18178   1 "New York" 18179   4 "New York" 18180   0 "New York" 18181  10 "New York" 18182  -4 "New York" 18184   1 "New York" 18186   2 "New York" 18187   5 "New York" 18188 -10 "New York" 18189  -5 "New York" 18190   4 "New York" 18191   1 "New York" 18192  -5 "New York" 18194  -1 "New York" 18195   4 "New York" 18196   0 "New York" 18198 -13 "New York" 18199   9 "New York" 18200  -4 "New York" 18201   6 "New York" 18202  -1 "New York" 18203  -7 "New York" 18204  -1 "New York" 18205   4 "New York" 18206   1 "New York" 18207  -4 "New York" 18208  -3 "New York" 18209  -5 "New York" 18210  -2 "New York" 18212  -4 "New York" 18213   0 "New York" 18214  -3 "New York" 18215  -1 "New York" 18216  -5 "New York" 18217   0 "New York" 18218  -3 "New York" 18219   0 "New York" 18220  -1 "New York" 18221  -1 "New York" 18222  -3 "New York" 18223  -2 "New York" 18224   2 "New York" 18225  -4 "New York" 18226  -3 "New York" 18227  -5 "New York" 18228  -3 "New York" 18229  -1 "New York" 18230   1 "New York" 18231  -1 "New York" 18232   2 "New York" 18233  -5 "New York" 18234   6 "New York" 18235   5 "New York" 18236   1 "New York" 18237   4 "New York" 18238   3 "New York" 18239   1 "New York" 18240  -3 "New York" 18242  -1 "New York" 18243   1 "New York" 18244  -2 "New York" 18245  -2 "New York" 18246  -4 "New York" 18247  -1 "New York" 18248  -2 "New York" 18249   1 "New York" 18250  -1 "New York" 18251  -3 "New York" 18252   6 "New York" 18253   2 "New York" 18254   0 "New York" 18256  -2 "New York" 18257  -3 "New York" 18258  -3 "New York" 18259  -1 "New York" 18260   3 "New York" 18261   0 "New York" 18262  -1 "New York" 18263   2 "New York" 18264  -1 "New York" 18265   2 "New York" 18266   1 "New York" 18267  -1 "New York" 18268   0 "New York" 18269   1 "New York" 18270  -4 "New York" 18271  11 "New York" 18272  -2 "New York" 18273  -2 "New York" 18274   1 "New York" 18275   0 "New York" 18276  -2 "New York" 18277  -4 "New York" 18278  -3 "New York" 18279  11 "New York" 18280   0 "New York" 18281   0 "New York" 18282  -1 "New York" 18283   0 "New York" 18284  -5 "New York" 18285  -3 "New York" 18286 -12 "New York" 18287  -1 "New York" 18288   0 "New York" 18289   0 "New York" 18290  -3 "New York" 18291  -1 "New York" 18292  -4 "New York" 18293  -5 "New York" 18295  -4 "New York" 18296   0 "New York" 18297  -2 "New York" 18298  -3 "New York" 18299  -5 "New York" 18301  -1 "New York" 18302  -3 "New York" 18303  -5 "New York" 18305   2 "New York" 18306   0 "New York" 18307   0 "New York" 18309   0 "New York" 18310  -4 "New York" 18312  -2 "New York" 18313  -2 "New York" 18314  -2 "New York" 18315  -2 "New York" 18316  -4 "New York" 18319   2 "New York" 18320   0 "New York" 18321  -2 "New York" 18322  -2 "New York" 18323   3 "New York" 18324   0 "New York" 18325  -2 "New York" 18326  -5 "New York" 18327  -2 "New York" 18328  -2 "New York" 18329   0 "New York" 18330  -1 "New York" 18331  -4 "New York" 18332  -1 "New York" 18333  -2 "New York" 18334   0 "New York" 18335   2 "New York" 18336   3 "New York" 18337  -2 "New York" 18338  -3 "New York" 18339  -2 "New York" 18340   1 "New York" 18341  -4 "New York" 18342  -3 "New York" 18343  -2 "New York" 18344   3 "New York" 18345   2 "New York" 18346   1 "New York" 18347   0 "New York" 18348  -1 "New York" 18349  -1 "New York" 18350   0 "New York" 18351  -1 "New York" 18352  -4 "New York" 18353   1 "New York" 18354  -2 "New York" 18355   5 "New York" 18356  -2 "New York" 18357   2 "New York" 18358   5 "New York" 18359  -3 "New York" 18360  -4 "New York" 18361   1 "New York" 18362  -2 "New York" 18363  -3 "New York" 18364   0 "New York" 18365   0 "New York" 18366 -10 "New York" 18367   4 "New York" 18368   2 "New York" 18369   4 "New York" 18370  -2 "New York" 18371   2 "New York" 18372   2 "New York" 18373   0 "New York" 18374  -2 "New York" 18375   0 "New York" 18376   4 "New York" 18377   0 "New York" 18379   2 "New York" 18380   4 "New York" 18381   2 "New York" 18382   4 "New York" 18383  -4 end format %td date

 * Example generated by -dataex-. To install: ssc install dataex clear input long date str100 city double ret 17899 "New York"     .03617462197315781 17902 "New York"     .00828476682888339 17903 "New York"     .02480302419822941 17904 "New York"    -.03177914951468083 17905 "New York"     .01044385229571439 17906 "New York"    -.03363200666141401 17909 "New York"    -.03493455057835021 17910 "New York"   .0036728301637600643 17911 "New York"    -.04193094121552541 17912 "New York"     .01104281357524234 17913 "New York"   .0037293494201803244 17917 "New York"     -.0632596459038706 17918 "New York"      .0543050358201354 17919 "New York"      -.031135625869176 17920 "New York"    .006600286300533259 17923 "New York"   .0037234682222446486 17924 "New York"    .012558578273139732 17925 "New York"     .04936038080591315 17926 "New York"   -.041499313027800425 17927 "New York"    -.01993873702403395 17930 "New York"    .007991917872973516 17931 "New York"     .01179691761557117 17932 "New York"   -.006234569172627981 17933 "New York"    .022692048504062125 17934 "New York"     .03688453932707444 17937 "New York"    -.00671912721768224 17938 "New York"   -.052649036891718584 17939 "New York"    .016662555767341825 17940 "New York"   .0013688577943545136 17941 "New York"   -.008992326641995115 17945 "New York"     -.0469228984387295 17946 "New York"   -.004660804703901631 17947 "New York"   -.020578845064242526 17948 "New York"   -.016881606410516378 17951 "New York"    -.03527986070629996 17952 "New York"    .053161046005178444 17953 "New York"    -.02147761793692903 17954 "New York"   -.008575922017049346 17955 "New York"   -.009988868698732268 17958 "New York"    -.06064399769567662 17959 "New York"   -.009540539649561024 17960 "New York"    .028233498263661606 17961 "New York"      -.057216359169717 17962 "New York"    .008872219412341738 17965 "New York"    -.02515900938052927 17966 "New York"     .07852483728447907 17967 "New York"    .002006807656284792 17968 "New York"     .05398129722029723 17969 "New York"    .014610341452177822 17972 "New York"  -.0030534557422611467 17973 "New York"     .03750698134431981 17974 "New York"     .03937643750211933 17975 "New York"   -.008967452746546995 17976 "New York"   -.019626340798105213 17979 "New York"     .08464470455544203 17980 "New York"    -.02248976630409449 17981 "New York"    .026815510568801014 17982 "New York"      .0398771831604796 17983 "New York"   -.028991999890603217 17986 "New York"     -.0404295533085161 17987 "New York"     .01968865858621679 17988 "New York"      .0208166102988657 17989 "New York"    .051453068023778914 17990 "New York"    .022908933995427528 17993 "New York"   -.019470020314289533 17994 "New York"   -.026165521043130077 17995 "New York"     .02049210768045499 17996 "New York"     .06444727504077824 18000 "New York"    .017415204820416936 18001 "New York"   -.020723507765634273 18002 "New York"    .020130075425739684 18003 "New York"    .021579537678420288 18004 "New York"    .017958262334042664 18007 "New York"   -.060020346793936134 18008 "New York"     .03816999908068842 18009 "New York"   .0011499659852894828 18010 "New York"  9.877236785225408e-06 18011 "New York"     .02346269182532484 18014 "New York"   -.019136495402483928 18015 "New York"    .005685517753890228 18016 "New York"     .03421365567738796 18017 "New York"    .007677977764581313 18018 "New York"   .0058268790371189625 18021 "New York"    .045327527679557916 18022 "New York"   .0017796475223340789 18023 "New York"     .01551583275467921 18024 "New York"   -.024642894130926116 18025 "New York"     .03831480162176366 18028 "New York"   -.021202600950570662 18029 "New York"   -.009661035309560292 18030 "New York"    -.04490298052378758 18031 "New York"    .020010462192456342 18032 "New York"   -.006841705468868446 18035 "New York"    .038781008987657344 18036 "New York"   .0029847389679038493 18037 "New York"   -.006865112838119033 18038 "New York"   -.019178130444395067 18039 "New York"   -.002778820750499119 18043 "New York"      .0374282577515574 18044 "New York"    -.01888940330015576 18045 "New York"    .011929060846285458 18046 "New York"    .020830052672942127 18049 "New York"     .03577298502519333 18050 "New York"    .002808465416078602 18051 "New York"   -.011530855399833843 18052 "New York"    .022561302895071463 18053 "New York"  .00018293352367896817 18056 "New York"    -.00532620237550244 18057 "New York"    .003421072042755564 18058 "New York"   -.002867812794535478 18059 "New York"    .008736139186280693 18060 "New York"    .004002550228270357 18063 "New York"    -.02407717492746155 18064 "New York"   -.017547832579370107 18065 "New York" -.00043465411731575176 18066 "New York"    .006940478971731032 18067 "New York"     .00519346476968717 18070 "New York"     -.0365268077922663 18071 "New York"  -.0016077405605666385 18072 "New York"    .014099059218035198 18073 "New York"    .025965849477668397 18074 "New York"    .006363995959432338 18077 "New York"    .006866360280342812 18078 "New York"   -.006139030953779234 18079 "New York"    .008873944690378359 18080 "New York"    -.03517197949783817 18084 "New York"  -.0021171172391151703 18085 "New York"   -.019494730613011678 18086 "New York"   -.011754058753110799 18087 "New York"  -.0004634072989758835 18088 "New York"   -.003650136584768962 18091 "New York"    .024301506313948472 18092 "New York"     .01233438697320857 18093 "New York"    .032336041839687225 18094 "New York"    .004741131963909757 18095 "New York" -.00046030858628668444 18098 "New York"     .01764272706410177 18099 "New York"   -.002480308592313594 18100 "New York"    .010841310560610072 18101 "New York"    .026723795082782803 18102 "New York"    .007193144195701314 18105 "New York"    .009083624267906446 18106 "New York"   -.001226102761980264 18107 "New York"  -.0046760025262888635 18108 "New York"    .020350343235908373 18109 "New York"   .0022610908224492106 18112 "New York"    .019300861053366702 18113 "New York"   .0020708948936714214 18114 "New York"    .004715639687087269 18115 "New York"   .0030413715477279075 18116 "New York"      .0327873367537513 18119 "New York"  -.0018778608565195168 18120 "New York"   -.018459970244616153 18121 "New York"    .016281858673636046 18122 "New York"    .006844561733546985 18123 "New York"   -.016727759324983636 18126 "New York"   -.030275386179387687 18127 "New York"    .016247433560526093 18128 "New York"    .010806536358336128 18129 "New York"    .013285494738439308 18130 "New York"    .025768211232078307 18133 "New York"  -.0029559322506487015 18134 "New York"    .009512640270028747 18135 "New York"    .002401153570265049 18136 "New York"    .009783766483865926 18137 "New York"    -.00024365575886862 18140 "New York"   -.006859081768197939 18141 "New York"   -.024784967215227764 18142 "New York"   -.006147590661281425 18143 "New York"    .011262827708228946 18144 "New York"    .012678075593071356 18148 "New York"   .0070439869802462855 18149 "New York"    .014058247151677025 18150 "New York"    .011418861087597317 18151 "New York"   -.002722643082040433 18154 "New York"    .011581666480795528 18155 "New York"    .008475934905906302 18156 "New York"     .02498213384673294 18157 "New York"   -.000565205163011457 18158 "New York"    .005578007556876032 18161 "New York"   -.002311680986858386 18162 "New York"    .011184678644699316 18163 "New York"   -.009215745097541993 18164 "New York"   -.014285494208327035 18165 "New York"   -.005371744070302318 18168 "New York"     .02806237193749727 18169 "New York"   .0032060456602565966 18170 "New York"    -.01156138565829103 18171 "New York"   -.029063440513135374 18172 "New York"   -.005688476074474717 18175 "New York"    .018118603595643146 18176 "New York"    .020497522086818782 18177 "New York"   .0014956261551093888 18178 "New York"    .011577130783310071 18179 "New York"    .004828208792739468 18182 "New York"  -.0000959818831975817 18183 "New York"  -.0011983679035821343 18184 "New York"    .017891241065704458 18185 "New York"    .003922701510299176 18186 "New York"   -.009779332971734939 18189 "New York"    .011420601597484691 18190 "New York"   -.012843800763412697 18191 "New York"   -.011277093164512496 18192 "New York"    .015971728510258368 18193 "New York"    -.01713198729282196 18196 "New York"   -.015113852266375417 18197 "New York"   -.011130559889926716 18198 "New York"    -.03470006924640314 18199 "New York"     .02524553621218442 18200 "New York"    -.03063673195375534 18203 "New York"   .0013742896060552755 18204 "New York"    .011274491419929913 18205 "New York"   -.009065342494164054 18206 "New York"     .03125740944041785 18207 "New York"  -.0004071565487595531 18210 "New York"     .02220062334303519 18211 "New York"   -.008456419262657184 18212 "New York"   .0028192878160945268 18213 "New York"   -.014847128364041423 18214 "New York"    .004383731453095143 18217 "New York"    .025151887740232234 18218 "New York"  -.0027299005143706584 18219 "New York"   -.004991882161109471 18220 "New York"   -.012969740701271423 18221 "New York"  -.0014234610098737659 18224 "New York"    .011679819522557066 18225 "New York"  -.0020784935721214377 18226 "New York"    .004008108400550743 18228 "New York"   -.020577022628541188 18231 "New York"   .0022253652956327665 18232 "New York"      .0143408199057683 18233 "New York"    .006095933794821068 18234 "New York"   -.014885720160000582 18235 "New York"    .015135290272852429 18238 "New York"  -.0009082940485715479 18239 "New York"   -.008821189359311232 18240 "New York"    .002079375009974707 18241 "New York"  -.0025125747248333843 18242 "New York"    .004638903637383826 18245 "New York"     .01289311491135054 18246 "New York"   -.006375764560568944 18247 "New York"     .00728003236583437 18248 "New York"   -.013280151973585582 18249 "New York"    .005767221718717617 18252 "New York"    .012869234749878626 18253 "New York"    .001699413459228265 18254 "New York"    .006078374628813412 18255 "New York"    .005548999415264296 18259 "New York"  -.0006110355760215614 18260 "New York"    .005157373683640542 18261 "New York" -.00010532286264956034 18262 "New York"   -.006996809281796838 18266 "New York"    .022605563804783933 18267 "New York"    .001080200198527065 18268 "New York"   .0071220492260650765 18269 "New York"   .0047847048276678595 18270 "New York"   .0019305475270225397 18273 "New York" -.00019994603691532488 18274 "New York"   -.015760572405997663 18275 "New York"    .009904776412182518 18276 "New York"     .00202668912524748 18277 "New York"   -.011807075177878892 18281 "New York"     .01066745151521548 18282 "New York"   -.010446518147364258 18283 "New York"   -.019079124555742788 18284 "New York"   -.019428344968248218 18287 "New York"    .002201648605912323 18288 "New York"   -.007067343725958058 18289 "New York"    .007523431207406364 18290 "New York"   -.009359845511930724 18291 "New York"   -.007003278559967575 18294 "New York"    .010366629217065977 18295 "New York"    .009852738303485356 18296 "New York"   -.001346376296808763 18297 "New York"   -.031921309025792946 18298 "New York"    .004667080665052298 18301 "New York"   -.010132452228883208 18302 "New York"    .011823824132725055 18303 "New York"     .00202701635501017 18304 "New York"    .016217447904637083 18305 "New York"   .0048416203355419645 18309 "New York"    .015070406398687433 18310 "New York"    .005640771611256358 18311 "New York"    .007515014434130325 18312 "New York"    .004701175835902969 18315 "New York"   .0013840551468652289 18316 "New York"   -.009105972816262064 18317 "New York"    .007636908154848079 18318 "New York"  -.0009660587275226509 18319 "New York"   .0019404741189926146 18322 "New York"     .01756242937587488 18323 "New York"    .003790499508632377 18324 "New York"    .001976346306410284 18325 "New York"     .04972781030086574 18326 "New York"    .019517682158855163 18329 "New York"    .011512328446723329 18330 "New York"    .005772994106593661 18331 "New York"   .0033414691559021147 18332 "New York"    .006313959403017249 18333 "New York"  -.0001446308250216624 18336 "New York"   -.004041051079417603 18337 "New York"     .00914872222664342 18338 "New York"    .013015446296263597 18339 "New York"  -.0034660537859077197 18340 "New York"   -.009206551471064674 18343 "New York"    .005470451702888404 18344 "New York"    .010455765251744872 18345 "New York"   -.005612946540238157 18346 "New York"  -.0009593388490313187 18347 "New York"      .0059191059459315 18350 "New York"    .004561388761757348 18351 "New York"    .001990960960635372 18352 "New York"   -.005386052461033568 18353 "New York"   .0036080962770040957 18357 "New York"    .014126237491896005 18358 "New York"    .008571774737417885 18359 "New York"  -.0009203081179104818 18360 "New York"    .006632096600637911 18361 "New York"    .007188243116403313 18364 "New York"   .0018395061637562179 18365 "New York"    .005791027292244701 18366 "New York"    .019585098524717008 18367 "New York"    .003848112513075542 18368 "New York"   -.015485729964019776 18371 "New York"   .0010086568125354295 18372 "New York"    .010540918915762336 18373 "New York"    .001210695936494288 18374 "New York"    .010065820781353789 18375 "New York"     .00726943150173658 18378 "New York"   .0009238435234798893 18379 "New York"   -.025769049262434437 18380 "New York"   .0014042843727760815 18381 "New York"     .01747391247489133 18382 "New York"   -.021507307361885483 18385 "New York"     .01827343295584584 18386 "New York"    -.03003531776451016 18387 "New York"    -.01169618690742086 18388 "New York"    -.04214457047244461 18389 "New York"   -.023141002773485665 18392 "New York"     .04941868095193058 18393 "New York"    .005032636678978716 18394 "New York"     .02303829688328249 18395 "New York"   -.010598003232697412 18396 "New York"    -.02211726968011867 18399 "New York"   .0000811079656856449 18400 "New York"   -.018385651897277696 18401 "New York"   -.013079344234489775 18402 "New York"    -.04637939381328496 18403 "New York"    .012944447566207435 18406 "New York"    -.01171557164324641 18407 "New York"  -.0005444737087765878 18408 "New York"    .004594838196999853 18409 "New York"     .04033165592579292 18410 "New York"   -.008624180405651667 18414 "New York"   -.025142591536595402 18415 "New York"     .02261022608855727 18416 "New York"   .0025697747539445777 18417 "New York"   -.038701023504307326 18420 "New York"   -.023303616302490063 18421 "New York"  -.0020089906443049527 18422 "New York"  -.0027236249127769964 18423 "New York"    .030167329930942264 18424 "New York"    .011275950754242767 18427 "New York"   .0031631547948587444 18428 "New York"     .02759218119606814 end format %td date

Great thanks!!

[Edward Wu]

Here are the holidays for 2009 and 2010, I have 10 years data

NYSE 2010 Wall Street Stock Market Holiday

New Year's Day	January 1, 2010
Martin Luther King, Jr. Day	January 18, 2010
Washington's Birthday (Presidents' Day)	February 15, 2010
Good Friday	April 2, 2010
Memorial Day	May 31, 2010
Independence Day	July 5, 2010
Labor Day	September 6, 2010
Thanksgiving	November 25, 2010
Christmas	December 24, 2010

---

NYSE 2009 Wall Street Stock Market Holiday

New Year's Day	January 1, 2009
Martin Luther King, Jr. Day	January 19, 2009
Washington's Birthday (Presidents' Day)	February 16, 2009
Good Friday	April 10, 2009
Memorial Day	May 25, 2009
Independence Day	July 3, 2009
Labor Day	September 7, 2009
Thanksgiving	November 26, 2009
Christmas	December 25, 2009

[Jorrit Gosens]

You could do:

bys city_id (date): replace match_ret_score = L3.score+L2.score+L1.score if dayofweek==1 // take value of score for friday if day is a monday

For the mondays.

For you data examples, something went wrong when you posted them. This is a problem with the forum software, not something you did wrong. COuld you please post again, and when you post you dataex, make sure the opening and closing code blocks are on separate lines.
That is:
{CODE}
you dataex
{/CODE}

[Edward Wu]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 city float date double score
"New York" 18263   2
"New York" 18264  -1
"New York" 18265   2
"New York" 18266   1
"New York" 18267  -1
"New York" 18268   0
"New York" 18269   1
"New York" 18270  -4
"New York" 18271  11
"New York" 18272  -2
"New York" 18273  -2
"New York" 18274   1
"New York" 18275   0
"New York" 18276  -2
"New York" 18277  -4
"New York" 18278  -3
"New York" 18279  11
"New York" 18280   0
"New York" 18281   0
"New York" 18282  -1
"New York" 18283   0
"New York" 18284  -5
"New York" 18285  -3
"New York" 18286 -12
"New York" 18287  -1
"New York" 18288   0
"New York" 18289   0
"New York" 18290  -3
"New York" 18291  -1
"New York" 18292  -4
"New York" 18293  -5
"New York" 18295  -4
"New York" 18296   0
"New York" 18297  -2
"New York" 18298  -3
"New York" 18299  -5
"New York" 18301  -1
"New York" 18302  -3
"New York" 18303  -5
"New York" 18305   2
"New York" 18306   0
"New York" 18307   0
"New York" 18309   0
"New York" 18310  -4
"New York" 18312  -2
"New York" 18313  -2
"New York" 18314  -2
"New York" 18315  -2
"New York" 18316  -4
"New York" 18319   2
"New York" 18320   0
"New York" 18321  -2
"New York" 18322  -2
"New York" 18323   3
"New York" 18324   0
"New York" 18325  -2
"New York" 18326  -5
"New York" 18327  -2
"New York" 18328  -2
"New York" 18329   0
"New York" 18330  -1
"New York" 18331  -4
"New York" 18332  -1
"New York" 18333  -2
"New York" 18334   0
"New York" 18335   2
"New York" 18336   3
"New York" 18337  -2
"New York" 18338  -3
"New York" 18339  -2
"New York" 18340   1
"New York" 18341  -4
"New York" 18342  -3
"New York" 18343  -2
"New York" 18344   3
"New York" 18345   2
"New York" 18346   1
"New York" 18347   0
"New York" 18348  -1
"New York" 18349  -1
"New York" 18350   0
"New York" 18351  -1
"New York" 18352  -4
"New York" 18353   1
"New York" 18354  -2
"New York" 18355   5
"New York" 18356  -2
"New York" 18357   2
"New York" 18358   5
"New York" 18359  -3
"New York" 18360  -4
"New York" 18361   1
"New York" 18362  -2
"New York" 18363  -3
"New York" 18364   0
"New York" 18365   0
"New York" 18366 -10
"New York" 18367   4
"New York" 18368   2
"New York" 18369   4
"New York" 18370  -2
"New York" 18371   2
"New York" 18372   2
"New York" 18373   0
"New York" 18374  -2
"New York" 18375   0
"New York" 18376   4
"New York" 18377   0
"New York" 18379   2
"New York" 18380   4
"New York" 18381   2
"New York" 18382   4
"New York" 18383  -4
"New York" 18384   0
"New York" 18385   0
"New York" 18386   4
"New York" 18387   0
"New York" 18389   4
"New York" 18390   4
"New York" 18391  -4
"New York" 18392  -4
"New York" 18393   2
"New York" 18394  -2
"New York" 18395  -4
"New York" 18396   0
"New York" 18397   0
"New York" 18398  -4
"New York" 18399   4
"New York" 18400  -4
"New York" 18401  -4
"New York" 18402   0
"New York" 18403   0
"New York" 18404   0
"New York" 18405   0
"New York" 18407   4
"New York" 18408   4
"New York" 18409   0
"New York" 18410   0
"New York" 18411  -4
"New York" 18412   4
"New York" 18413   0
"New York" 18414   4
"New York" 18415   0
"New York" 18416   2
"New York" 18417   0
"New York" 18418   0
"New York" 18419   4
"New York" 18421   4
"New York" 18422   2
"New York" 18423  -2
"New York" 18424   4
"New York" 18425   4
"New York" 18426   4
"New York" 18428   4
"New York" 18429   0
"New York" 18430   0
"New York" 18431   0
"New York" 18432   0
"New York" 18433   0
"New York" 18434  -2
"New York" 18435   4
"New York" 18436   4
"New York" 18437  -2
"New York" 18438   4
"New York" 18439  -4
"New York" 18440   4
"New York" 18441  -2
"New York" 18442  -4
"New York" 18443   0
"New York" 18444   0
"New York" 18445   0
"New York" 18446   0
"New York" 18447   4
"New York" 18448   0
"New York" 18449   4
"New York" 18450   0
"New York" 18451   2
"New York" 18452   0
"New York" 18453  -4
"New York" 18454   4
"New York" 18458  -2
"New York" 18459   0
"New York" 18460  -4
"New York" 18461   4
"New York" 18462  -2
"New York" 18463  -4
"New York" 18464   0
"New York" 18465   0
"New York" 18466   4
"New York" 18467  -4
"New York" 18468   0
"New York" 18469   2
"New York" 18470   0
"New York" 18471   0
"New York" 18472   4
"New York" 18473  -4
"New York" 18474   4
"New York" 18475  -4
"New York" 18476  -4
"New York" 18477   0
"New York" 18478   0
"New York" 18480  -4
"New York" 18481   4
"New York" 18482   0
"New York" 18483  -2
"New York" 18484   0
"New York" 18485   0
"New York" 18486   4
"New York" 18487   0
"New York" 18488   0
"New York" 18489  -4
"New York" 18490   0
"New York" 18491   0
"New York" 18492   4
"New York" 18493   0
"New York" 18494   0
"New York" 18495   4
"New York" 18496   0
"New York" 18497  -2
"New York" 18498   4
"New York" 18499  -4
"New York" 18500  -2
"New York" 18501   0
"New York" 18502   0
"New York" 18503   4
"New York" 18504   0
"New York" 18505   0
"New York" 18506   0
"New York" 18507   4
"New York" 18508   0
"New York" 18509   0
"New York" 18510   0
"New York" 18511  -4
"New York" 18512   0
"New York" 18513   4
"New York" 18515  -4
"New York" 18516   0
"New York" 18517   1
"New York" 18518  -5
"New York" 18519   4
"New York" 18520   0
"New York" 18521   2
"New York" 18522   0
"New York" 18523   0
"New York" 18524  -4
"New York" 18525   2
"New York" 18526   0
"New York" 18527  -4
"New York" 18528  -2
"New York" 18529  -4
"New York" 18530   0
"New York" 18531   4
"New York" 18532  -2
"New York" 18533   4
"New York" 18534  -6
"New York" 18535  -2
"New York" 18536   2
"New York" 18537   2
"New York" 18538   6
"New York" 18541   4
"New York" 18542   4
"New York" 18543  -1
"New York" 18544   3
"New York" 18545   5
"New York" 18546   4
"New York" 18548   0
"New York" 18550   1
"New York" 18551  -4
"New York" 18552  10
"New York" 18553  -4
"New York" 18554  -4
"New York" 18555   4
"New York" 18556   3
"New York" 18557  -4
"New York" 18558  -1
"New York" 18559   0
"New York" 18560   5
"New York" 18562   1
"New York" 18564  -1
"New York" 18565  -3
"New York" 18566  -7
"New York" 18567   0
"New York" 18569  -2
"New York" 18570   0
"New York" 18571   0
"New York" 18572  -3
"New York" 18573   7
"New York" 18575  -5
"New York" 18576  -2
"New York" 18577   0
"New York" 18578  -1
"New York" 18579  -3
"New York" 18580  -1
"New York" 18581   2
"New York" 18582  -2
"New York" 18583  -2
"New York" 18584  -1
"New York" 18585  -1
"New York" 18586  -1
"New York" 18587  -1
"New York" 18588   2
"New York" 18589   4
"New York" 18590  -1
"New York" 18591   5
"New York" 18592   1
"New York" 18593  -2
"New York" 18594   9
"New York" 18595  -1
"New York" 18596   0
"New York" 18597  -2
"New York" 18598  -1
"New York" 18599   0
"New York" 18600  -1
"New York" 18601   3
"New York" 18602  -4
"New York" 18603  -2
"New York" 18604   2
"New York" 18605  -2
"New York" 18606   1
"New York" 18607  -3
"New York" 18608  -4
"New York" 18609   4
"New York" 18610  -2
"New York" 18611   0
"New York" 18612   4
"New York" 18613  -5
"New York" 18614  -5
"New York" 18615   2
"New York" 18617   1
"New York" 18618   1
"New York" 18619  -1
"New York" 18621   2
"New York" 18622 -10
"New York" 18623  -2
"New York" 18624  -2
"New York" 18625  -1
"New York" 18626  -2
"New York" 18627   0
end
format %td date

* Example generated by -dataex-. To install: ssc install dataex
clear
input long date str100 city double ret
18266 "New York"    .022605563804783933
18267 "New York"    .001080200198527065
18268 "New York"   .0071220492260650765
18269 "New York"   .0047847048276678595
18270 "New York"   .0019305475270225397
18273 "New York" -.00019994603691532488
18274 "New York"   -.015760572405997663
18275 "New York"    .009904776412182518
18276 "New York"     .00202668912524748
18277 "New York"   -.011807075177878892
18281 "New York"     .01066745151521548
18282 "New York"   -.010446518147364258
18283 "New York"   -.019079124555742788
18284 "New York"   -.019428344968248218
18287 "New York"    .002201648605912323
18288 "New York"   -.007067343725958058
18289 "New York"    .007523431207406364
18290 "New York"   -.009359845511930724
18291 "New York"   -.007003278559967575
18294 "New York"    .010366629217065977
18295 "New York"    .009852738303485356
18296 "New York"   -.001346376296808763
18297 "New York"   -.031921309025792946
18298 "New York"    .004667080665052298
18301 "New York"   -.010132452228883208
18302 "New York"    .011823824132725055
18303 "New York"     .00202701635501017
18304 "New York"    .016217447904637083
18305 "New York"   .0048416203355419645
18309 "New York"    .015070406398687433
18310 "New York"    .005640771611256358
18311 "New York"    .007515014434130325
18312 "New York"    .004701175835902969
18315 "New York"   .0013840551468652289
18316 "New York"   -.009105972816262064
18317 "New York"    .007636908154848079
18318 "New York"  -.0009660587275226509
18319 "New York"   .0019404741189926146
18322 "New York"     .01756242937587488
18323 "New York"    .003790499508632377
18324 "New York"    .001976346306410284
18325 "New York"     .04972781030086574
18326 "New York"    .019517682158855163
18329 "New York"    .011512328446723329
18330 "New York"    .005772994106593661
18331 "New York"   .0033414691559021147
18332 "New York"    .006313959403017249
18333 "New York"  -.0001446308250216624
18336 "New York"   -.004041051079417603
18337 "New York"     .00914872222664342
18338 "New York"    .013015446296263597
18339 "New York"  -.0034660537859077197
18340 "New York"   -.009206551471064674
18343 "New York"    .005470451702888404
18344 "New York"    .010455765251744872
18345 "New York"   -.005612946540238157
18346 "New York"  -.0009593388490313187
18347 "New York"      .0059191059459315
18350 "New York"    .004561388761757348
18351 "New York"    .001990960960635372
18352 "New York"   -.005386052461033568
18353 "New York"   .0036080962770040957
18357 "New York"    .014126237491896005
18358 "New York"    .008571774737417885
18359 "New York"  -.0009203081179104818
18360 "New York"    .006632096600637911
18361 "New York"    .007188243116403313
18364 "New York"   .0018395061637562179
18365 "New York"    .005791027292244701
18366 "New York"    .019585098524717008
18367 "New York"    .003848112513075542
18368 "New York"   -.015485729964019776
18371 "New York"   .0010086568125354295
18372 "New York"    .010540918915762336
18373 "New York"    .001210695936494288
18374 "New York"    .010065820781353789
18375 "New York"     .00726943150173658
18378 "New York"   .0009238435234798893
18379 "New York"   -.025769049262434437
18380 "New York"   .0014042843727760815
18381 "New York"     .01747391247489133
18382 "New York"   -.021507307361885483
18385 "New York"     .01827343295584584
18386 "New York"    -.03003531776451016
18387 "New York"    -.01169618690742086
18388 "New York"    -.04214457047244461
18389 "New York"   -.023141002773485665
18392 "New York"     .04941868095193058
18393 "New York"    .005032636678978716
18394 "New York"     .02303829688328249
18395 "New York"   -.010598003232697412
18396 "New York"    -.02211726968011867
18399 "New York"   .0000811079656856449
18400 "New York"   -.018385651897277696
18401 "New York"   -.013079344234489775
18402 "New York"    -.04637939381328496
18403 "New York"    .012944447566207435
18406 "New York"    -.01171557164324641
18407 "New York"  -.0005444737087765878
18408 "New York"    .004594838196999853
18409 "New York"     .04033165592579292
18410 "New York"   -.008624180405651667
18414 "New York"   -.025142591536595402
18415 "New York"     .02261022608855727
18416 "New York"   .0025697747539445777
18417 "New York"   -.038701023504307326
18420 "New York"   -.023303616302490063
18421 "New York"  -.0020089906443049527
18422 "New York"  -.0027236249127769964
18423 "New York"    .030167329930942264
18424 "New York"    .011275950754242767
18427 "New York"   .0031631547948587444
18428 "New York"     .02759218119606814
18429 "New York"   -.004829495688941928
18430 "New York"  -.0010966489919462823
18431 "New York"    .002622402975768117
18434 "New York"   -.013360085850205993
18435 "New York"   -.014697844110196456
18436 "New York"   -.001712334961179764
18437 "New York"   -.018499093844870765
18438 "New York"     .01599293242944705
18441 "New York"   -.005646290565885964
18442 "New York"    -.03924344122304671
18443 "New York"  -.0069901248425125115
18444 "New York"   -.008552062605052261
18445 "New York"   -.008834195912686768
18449 "New York"   -.008138291900552833
18450 "New York"    .028224449185581302
18451 "New York"    .010679842796214745
18452 "New York"    .016580798016508174
18455 "New York"    -.01078036835683662
18456 "New York"    .025613399405353086
18457 "New York"  -.0023927881679667523
18458 "New York"   -.000766875204820412
18459 "New York"   -.033552793811189606
18462 "New York"  .00019041903986179696
18463 "New York"    .014169251377864173
18464 "New York"   -.011212320251999138
18465 "New York"    .029824846160946612
18466 "New York"     .02180442369415618
18469 "New York"    .019112483550700703
18470 "New York"   -.005257807276284896
18471 "New York"   -.011086039162637461
18472 "New York"   .0025447222444199722
18473 "New York"   .0026402341234643247
18476 "New York"     .01660144120978657
18477 "New York"   -.012386475131643237
18478 "New York"    .011661467794328927
18479 "New York"  -.0014967430952765425
18480 "New York" -.00033519705137905473
18483 "New York"    .006796884883386054
18484 "New York"   -.013673937557733716
18485 "New York"    -.03434104360231511
18486 "New York"  -.0028173329100309834
18487 "New York"   -.008698001655962295
18490 "New York"   .0022458205860823874
18491 "New York"    .010557511860373958
18492 "New York"   .0012139855254510487
18493 "New York"    -.01754401884062207
18494 "New York"  -.0059764783398821705
18497 "New York"   -.010145412977169739
18498 "New York"   -.017052110711782364
18499 "New York"    .015424922706895612
18500 "New York"    -.00665052911107871
18501 "New York"    .023079525580899905
18504 "New York"    -.02249106164176569
18505 "New York"    .000974289069195094
18506 "New York"    .029569530067677943
18507 "New York"    .012711134460488088
18508 "New York"    .017849129423273463
18512 "New York"   -.020769819677275616
18513 "New York"    .010597369578641115
18514 "New York"    .004762655679825665
18515 "New York"   .0032918867586830196
18518 "New York"     .02028160305071427
18519 "New York"   .0030804506961745284
18520 "New York"   .0028364918365202164
18521 "New York" -.00007116804718626318
18522 "New York"   .0019178254535942006
18525 "New York"     .02149956788936699
18526 "New York"   -.005928402040965774
18527 "New York"   -.012244619440968597
18528 "New York"   -.006732209841612968
18529 "New York"     .02711401620325449
18532 "New York"   -.002818264116218348
18533 "New York"    .012307893406681682
18534 "New York"  -.0013261517083321204
18535 "New York"   -.001884333009507423
18536 "New York"    .007993035434534954
18539 "New York"   -.008695052585549533
18540 "New York"     .02206604354489114
18541 "New York"  -.0015549918117504866
18542 "New York"   -.001488345900678166
18543 "New York"    .013549586596538222
18546 "New York"    .002505254220675617
18547 "New York"    .008222122974662089
18548 "New York"     .01610613954600863
18549 "New York"   -.004230721999098727
18550 "New York"  -.0006487704591446316
18553 "New York"    .009640780359792012
18554 "New York"   -.022760312963322166
18555 "New York"    .011578625541428224
18556 "New York"   -.000482307939279464
18557 "New York"    .002210323149037938
18560 "New York"    .006063948063432866
18561 "New York"    .005846108173606016
18562 "New York"   -.004159989588867662
18563 "New York"  -.0027827162926475847
18564 "New York"   .0068274913603560096
18567 "New York"    -.00337473546706831
18568 "New York"    .014051079509719726
18569 "New York"   .0020142223500090864
18570 "New York"    .019600917449626592
18571 "New York"    .013986024275542268
18574 "New York"    .003262642383455269
18575 "New York"    -.01529693110102427
18576 "New York"    .006168071230246325
18577 "New York"   -.004247709353184027
18578 "New York"   -.011872850325028507
18581 "New York"   .0010183439224361321
18582 "New York"   -.021349621131159967
18583 "New York"   .0023037180347713613
18584 "New York"    .015266349170011498
18585 "New York"    .001335715793099815
18588 "New York" -.00047193222187249173
18589 "New York"   -.009042660092405465
18590 "New York"    .018931539637607433
18592 "New York"   -.004240148207229081
18595 "New York"   -.002653384262382237
18596 "New York"   -.005885290077534474
18597 "New York"    .018812019528911237
18598 "New York"    .008081187532685186
18599 "New York"     .00373398326430619
18602 "New York"    .004120680034900896
18603 "New York"    .003400790571900777
18604 "New York"   .0024737032262913627
18605 "New York"    .005746661046976639
18606 "New York"    .006205812382142343
18609 "New York"  -.0021001614109921836
18610 "New York"    .002165714197808426
18611 "New York"   -.004957533764823823
18612 "New York"    .005602185287633675
18613 "New York"    .002531867655203383
18616 "New York"   .0014361551457344071
18617 "New York"    .008968520421782344
18618 "New York"   .0021904466177861477
18619 "New York"   -.001119769428844882
18623 "New York"   .0017249050087448728
18624 "New York"  -.0021318418289785214
18625 "New York"   .0015788437349904517
18626 "New York"  -.0007556081080202844
18627 "New York"   .0004164110688651806
end
format %td date

[Edward Wu]

Thanks! Here are the examples for NYC in year 2010. There are 338 observations of score, and 252 observations for return, because of 252 trading days in total in 2010.

[Jorrit Gosens]

So, assuming both weekends and holidays are simply identified by missing values for ret, you could do:

merge 1:1 date city using `something' // use actual filenam
gen dayofweek = dow(date) // gives numerical values with 0=Sunday,...,6=Saturday
egen city_id = group(city)
xtset city_id date
bys city_id (date): gen score_lagged = L1.score
bys city_id (date): replace score_lagged = L1.score+L2.score if L1.ret==.
bys city_id (date): replace score_lagged = L1.score+L2.score+L3.score if L1.ret==. & L2.ret==. 
bys city_id (date): replace score_lagged = L1.score+L2.score+L3.score+L4.score if L1.ret==. & L2.ret==. & L3.ret==.

And if there are longer holidays then 3 consecutive days you'll probably understand how to include those.

[Edward Wu]

Awesome! it's very close now. The only remaining issue is that, the code won't work for those non-business days with no score. For example if there are scores available on Friday and Saturday, but not available on Sunday. The result would be the score of Saturday only, not the sum of score of Friday and Saturday, which is the actual one wanted. If there is only one city, it's easy to fill the missing dates, what about for multiple cities?

I appreciate your time!

[Clyde Schechter]

Sorry to come late to the party. Here's some code that I think works without having to explicitly make special cases for particular days. This code just looks back and adds up all the scores for all the days with missing returns until the preceding date that has a non-missing return. If I understand the question correctly it is what is wanted. The only place it will fail is the first observation for a city if the city's data begins with missing scores, because the backwards search for a non-missing score turns up nothing. But I'm not sure how you want to handle that situation in any case.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str8 city float date double score
"New York" 18263   2
"New York" 18264  -1
"New York" 18265   2
"New York" 18266   1
"New York" 18267  -1
"New York" 18268   0
"New York" 18269   1
"New York" 18270  -4
"New York" 18271  11
"New York" 18272  -2
"New York" 18273  -2
"New York" 18274   1
"New York" 18275   0
"New York" 18276  -2
"New York" 18277  -4
"New York" 18278  -3
"New York" 18279  11
"New York" 18280   0
"New York" 18281   0
"New York" 18282  -1
"New York" 18283   0
"New York" 18284  -5
"New York" 18285  -3
"New York" 18286 -12
"New York" 18287  -1
"New York" 18288   0
"New York" 18289   0
"New York" 18290  -3
"New York" 18291  -1
"New York" 18292  -4
"New York" 18293  -5
"New York" 18295  -4
"New York" 18296   0
"New York" 18297  -2
"New York" 18298  -3
"New York" 18299  -5
"New York" 18301  -1
"New York" 18302  -3
"New York" 18303  -5
"New York" 18305   2
"New York" 18306   0
"New York" 18307   0
"New York" 18309   0
"New York" 18310  -4
"New York" 18312  -2
"New York" 18313  -2
"New York" 18314  -2
"New York" 18315  -2
"New York" 18316  -4
"New York" 18319   2
"New York" 18320   0
"New York" 18321  -2
"New York" 18322  -2
"New York" 18323   3
"New York" 18324   0
"New York" 18325  -2
"New York" 18326  -5
"New York" 18327  -2
"New York" 18328  -2
"New York" 18329   0
"New York" 18330  -1
"New York" 18331  -4
"New York" 18332  -1
"New York" 18333  -2
"New York" 18334   0
"New York" 18335   2
"New York" 18336   3
"New York" 18337  -2
"New York" 18338  -3
"New York" 18339  -2
"New York" 18340   1
"New York" 18341  -4
"New York" 18342  -3
"New York" 18343  -2
"New York" 18344   3
"New York" 18345   2
"New York" 18346   1
"New York" 18347   0
"New York" 18348  -1
"New York" 18349  -1
"New York" 18350   0
"New York" 18351  -1
"New York" 18352  -4
"New York" 18353   1
"New York" 18354  -2
"New York" 18355   5
"New York" 18356  -2
"New York" 18357   2
"New York" 18358   5
"New York" 18359  -3
"New York" 18360  -4
"New York" 18361   1
"New York" 18362  -2
"New York" 18363  -3
"New York" 18364   0
"New York" 18365   0
"New York" 18366 -10
"New York" 18367   4
"New York" 18368   2
"New York" 18369   4
"New York" 18370  -2
"New York" 18371   2
"New York" 18372   2
"New York" 18373   0
"New York" 18374  -2
"New York" 18375   0
"New York" 18376   4
"New York" 18377   0
"New York" 18379   2
"New York" 18380   4
"New York" 18381   2
"New York" 18382   4
"New York" 18383  -4
"New York" 18384   0
"New York" 18385   0
"New York" 18386   4
"New York" 18387   0
"New York" 18389   4
"New York" 18390   4
"New York" 18391  -4
"New York" 18392  -4
"New York" 18393   2
"New York" 18394  -2
"New York" 18395  -4
"New York" 18396   0
"New York" 18397   0
"New York" 18398  -4
"New York" 18399   4
"New York" 18400  -4
"New York" 18401  -4
"New York" 18402   0
"New York" 18403   0
"New York" 18404   0
"New York" 18405   0
"New York" 18407   4
"New York" 18408   4
"New York" 18409   0
"New York" 18410   0
"New York" 18411  -4
"New York" 18412   4
"New York" 18413   0
"New York" 18414   4
"New York" 18415   0
"New York" 18416   2
"New York" 18417   0
"New York" 18418   0
"New York" 18419   4
"New York" 18421   4
"New York" 18422   2
"New York" 18423  -2
"New York" 18424   4
"New York" 18425   4
"New York" 18426   4
"New York" 18428   4
"New York" 18429   0
"New York" 18430   0
"New York" 18431   0
"New York" 18432   0
"New York" 18433   0
"New York" 18434  -2
"New York" 18435   4
"New York" 18436   4
"New York" 18437  -2
"New York" 18438   4
"New York" 18439  -4
"New York" 18440   4
"New York" 18441  -2
"New York" 18442  -4
"New York" 18443   0
"New York" 18444   0
"New York" 18445   0
"New York" 18446   0
"New York" 18447   4
"New York" 18448   0
"New York" 18449   4
"New York" 18450   0
"New York" 18451   2
"New York" 18452   0
"New York" 18453  -4
"New York" 18454   4
"New York" 18458  -2
"New York" 18459   0
"New York" 18460  -4
"New York" 18461   4
"New York" 18462  -2
"New York" 18463  -4
"New York" 18464   0
"New York" 18465   0
"New York" 18466   4
"New York" 18467  -4
"New York" 18468   0
"New York" 18469   2
"New York" 18470   0
"New York" 18471   0
"New York" 18472   4
"New York" 18473  -4
"New York" 18474   4
"New York" 18475  -4
"New York" 18476  -4
"New York" 18477   0
"New York" 18478   0
"New York" 18480  -4
"New York" 18481   4
"New York" 18482   0
"New York" 18483  -2
"New York" 18484   0
"New York" 18485   0
"New York" 18486   4
"New York" 18487   0
"New York" 18488   0
"New York" 18489  -4
"New York" 18490   0
"New York" 18491   0
"New York" 18492   4
"New York" 18493   0
"New York" 18494   0
"New York" 18495   4
"New York" 18496   0
"New York" 18497  -2
"New York" 18498   4
"New York" 18499  -4
"New York" 18500  -2
"New York" 18501   0
"New York" 18502   0
"New York" 18503   4
"New York" 18504   0
"New York" 18505   0
"New York" 18506   0
"New York" 18507   4
"New York" 18508   0
"New York" 18509   0
"New York" 18510   0
"New York" 18511  -4
"New York" 18512   0
"New York" 18513   4
"New York" 18515  -4
"New York" 18516   0
"New York" 18517   1
"New York" 18518  -5
"New York" 18519   4
"New York" 18520   0
"New York" 18521   2
"New York" 18522   0
"New York" 18523   0
"New York" 18524  -4
"New York" 18525   2
"New York" 18526   0
"New York" 18527  -4
"New York" 18528  -2
"New York" 18529  -4
"New York" 18530   0
"New York" 18531   4
"New York" 18532  -2
"New York" 18533   4
"New York" 18534  -6
"New York" 18535  -2
"New York" 18536   2
"New York" 18537   2
"New York" 18538   6
"New York" 18541   4
"New York" 18542   4
"New York" 18543  -1
"New York" 18544   3
"New York" 18545   5
"New York" 18546   4
"New York" 18548   0
"New York" 18550   1
"New York" 18551  -4
"New York" 18552  10
"New York" 18553  -4
"New York" 18554  -4
"New York" 18555   4
"New York" 18556   3
"New York" 18557  -4
"New York" 18558  -1
"New York" 18559   0
"New York" 18560   5
"New York" 18562   1
"New York" 18564  -1
"New York" 18565  -3
"New York" 18566  -7
"New York" 18567   0
"New York" 18569  -2
"New York" 18570   0
"New York" 18571   0
"New York" 18572  -3
"New York" 18573   7
"New York" 18575  -5
"New York" 18576  -2
"New York" 18577   0
"New York" 18578  -1
"New York" 18579  -3
"New York" 18580  -1
"New York" 18581   2
"New York" 18582  -2
"New York" 18583  -2
"New York" 18584  -1
"New York" 18585  -1
"New York" 18586  -1
"New York" 18587  -1
"New York" 18588   2
"New York" 18589   4
"New York" 18590  -1
"New York" 18591   5
"New York" 18592   1
"New York" 18593  -2
"New York" 18594   9
"New York" 18595  -1
"New York" 18596   0
"New York" 18597  -2
"New York" 18598  -1
"New York" 18599   0
"New York" 18600  -1
"New York" 18601   3
"New York" 18602  -4
"New York" 18603  -2
"New York" 18604   2
"New York" 18605  -2
"New York" 18606   1
"New York" 18607  -3
"New York" 18608  -4
"New York" 18609   4
"New York" 18610  -2
"New York" 18611   0
"New York" 18612   4
"New York" 18613  -5
"New York" 18614  -5
"New York" 18615   2
"New York" 18617   1
"New York" 18618   1
"New York" 18619  -1
"New York" 18621   2
"New York" 18622 -10
"New York" 18623  -2
"New York" 18624  -2
"New York" 18625  -1
"New York" 18626  -2
"New York" 18627   0
end
format %td date

tempfile scores
save `scores'

* Example generated by -dataex-. To install: ssc install dataex
clear
input long date str8 city double ret
18266 "New York"    .022605563804783933
18267 "New York"    .001080200198527065
18268 "New York"   .0071220492260650765
18269 "New York"   .0047847048276678595
18270 "New York"   .0019305475270225397
18273 "New York" -.00019994603691532488
18274 "New York"   -.015760572405997663
18275 "New York"    .009904776412182518
18276 "New York"     .00202668912524748
18277 "New York"   -.011807075177878892
18281 "New York"     .01066745151521548
18282 "New York"   -.010446518147364258
18283 "New York"   -.019079124555742788
18284 "New York"   -.019428344968248218
18287 "New York"    .002201648605912323
18288 "New York"   -.007067343725958058
18289 "New York"    .007523431207406364
18290 "New York"   -.009359845511930724
18291 "New York"   -.007003278559967575
18294 "New York"    .010366629217065977
18295 "New York"    .009852738303485356
18296 "New York"   -.001346376296808763
18297 "New York"   -.031921309025792946
18298 "New York"    .004667080665052298
18301 "New York"   -.010132452228883208
18302 "New York"    .011823824132725055
18303 "New York"     .00202701635501017
18304 "New York"    .016217447904637083
18305 "New York"   .0048416203355419645
18309 "New York"    .015070406398687433
18310 "New York"    .005640771611256358
18311 "New York"    .007515014434130325
18312 "New York"    .004701175835902969
18315 "New York"   .0013840551468652289
18316 "New York"   -.009105972816262064
18317 "New York"    .007636908154848079
18318 "New York"  -.0009660587275226509
18319 "New York"   .0019404741189926146
18322 "New York"     .01756242937587488
18323 "New York"    .003790499508632377
18324 "New York"    .001976346306410284
18325 "New York"     .04972781030086574
18326 "New York"    .019517682158855163
18329 "New York"    .011512328446723329
18330 "New York"    .005772994106593661
18331 "New York"   .0033414691559021147
18332 "New York"    .006313959403017249
18333 "New York"  -.0001446308250216624
18336 "New York"   -.004041051079417603
18337 "New York"     .00914872222664342
18338 "New York"    .013015446296263597
18339 "New York"  -.0034660537859077197
18340 "New York"   -.009206551471064674
18343 "New York"    .005470451702888404
18344 "New York"    .010455765251744872
18345 "New York"   -.005612946540238157
18346 "New York"  -.0009593388490313187
18347 "New York"      .0059191059459315
18350 "New York"    .004561388761757348
18351 "New York"    .001990960960635372
18352 "New York"   -.005386052461033568
18353 "New York"   .0036080962770040957
18357 "New York"    .014126237491896005
18358 "New York"    .008571774737417885
18359 "New York"  -.0009203081179104818
18360 "New York"    .006632096600637911
18361 "New York"    .007188243116403313
18364 "New York"   .0018395061637562179
18365 "New York"    .005791027292244701
18366 "New York"    .019585098524717008
18367 "New York"    .003848112513075542
18368 "New York"   -.015485729964019776
18371 "New York"   .0010086568125354295
18372 "New York"    .010540918915762336
18373 "New York"    .001210695936494288
18374 "New York"    .010065820781353789
18375 "New York"     .00726943150173658
18378 "New York"   .0009238435234798893
18379 "New York"   -.025769049262434437
18380 "New York"   .0014042843727760815
18381 "New York"     .01747391247489133
18382 "New York"   -.021507307361885483
18385 "New York"     .01827343295584584
18386 "New York"    -.03003531776451016
18387 "New York"    -.01169618690742086
18388 "New York"    -.04214457047244461
18389 "New York"   -.023141002773485665
18392 "New York"     .04941868095193058
18393 "New York"    .005032636678978716
18394 "New York"     .02303829688328249
18395 "New York"   -.010598003232697412
18396 "New York"    -.02211726968011867
18399 "New York"   .0000811079656856449
18400 "New York"   -.018385651897277696
18401 "New York"   -.013079344234489775
18402 "New York"    -.04637939381328496
18403 "New York"    .012944447566207435
18406 "New York"    -.01171557164324641
18407 "New York"  -.0005444737087765878
18408 "New York"    .004594838196999853
18409 "New York"     .04033165592579292
18410 "New York"   -.008624180405651667
18414 "New York"   -.025142591536595402
18415 "New York"     .02261022608855727
18416 "New York"   .0025697747539445777
18417 "New York"   -.038701023504307326
18420 "New York"   -.023303616302490063
18421 "New York"  -.0020089906443049527
18422 "New York"  -.0027236249127769964
18423 "New York"    .030167329930942264
18424 "New York"    .011275950754242767
18427 "New York"   .0031631547948587444
18428 "New York"     .02759218119606814
18429 "New York"   -.004829495688941928
18430 "New York"  -.0010966489919462823
18431 "New York"    .002622402975768117
18434 "New York"   -.013360085850205993
18435 "New York"   -.014697844110196456
18436 "New York"   -.001712334961179764
18437 "New York"   -.018499093844870765
18438 "New York"     .01599293242944705
18441 "New York"   -.005646290565885964
18442 "New York"    -.03924344122304671
18443 "New York"  -.0069901248425125115
18444 "New York"   -.008552062605052261
18445 "New York"   -.008834195912686768
18449 "New York"   -.008138291900552833
18450 "New York"    .028224449185581302
18451 "New York"    .010679842796214745
18452 "New York"    .016580798016508174
18455 "New York"    -.01078036835683662
18456 "New York"    .025613399405353086
18457 "New York"  -.0023927881679667523
18458 "New York"   -.000766875204820412
18459 "New York"   -.033552793811189606
18462 "New York"  .00019041903986179696
18463 "New York"    .014169251377864173
18464 "New York"   -.011212320251999138
18465 "New York"    .029824846160946612
18466 "New York"     .02180442369415618
18469 "New York"    .019112483550700703
18470 "New York"   -.005257807276284896
18471 "New York"   -.011086039162637461
18472 "New York"   .0025447222444199722
18473 "New York"   .0026402341234643247
18476 "New York"     .01660144120978657
18477 "New York"   -.012386475131643237
18478 "New York"    .011661467794328927
18479 "New York"  -.0014967430952765425
18480 "New York" -.00033519705137905473
18483 "New York"    .006796884883386054
18484 "New York"   -.013673937557733716
18485 "New York"    -.03434104360231511
18486 "New York"  -.0028173329100309834
18487 "New York"   -.008698001655962295
18490 "New York"   .0022458205860823874
18491 "New York"    .010557511860373958
18492 "New York"   .0012139855254510487
18493 "New York"    -.01754401884062207
18494 "New York"  -.0059764783398821705
18497 "New York"   -.010145412977169739
18498 "New York"   -.017052110711782364
18499 "New York"    .015424922706895612
18500 "New York"    -.00665052911107871
18501 "New York"    .023079525580899905
18504 "New York"    -.02249106164176569
18505 "New York"    .000974289069195094
18506 "New York"    .029569530067677943
18507 "New York"    .012711134460488088
18508 "New York"    .017849129423273463
18512 "New York"   -.020769819677275616
18513 "New York"    .010597369578641115
18514 "New York"    .004762655679825665
18515 "New York"   .0032918867586830196
18518 "New York"     .02028160305071427
18519 "New York"   .0030804506961745284
18520 "New York"   .0028364918365202164
18521 "New York" -.00007116804718626318
18522 "New York"   .0019178254535942006
18525 "New York"     .02149956788936699
18526 "New York"   -.005928402040965774
18527 "New York"   -.012244619440968597
18528 "New York"   -.006732209841612968
18529 "New York"     .02711401620325449
18532 "New York"   -.002818264116218348
18533 "New York"    .012307893406681682
18534 "New York"  -.0013261517083321204
18535 "New York"   -.001884333009507423
18536 "New York"    .007993035434534954
18539 "New York"   -.008695052585549533
18540 "New York"     .02206604354489114
18541 "New York"  -.0015549918117504866
18542 "New York"   -.001488345900678166
18543 "New York"    .013549586596538222
18546 "New York"    .002505254220675617
18547 "New York"    .008222122974662089
18548 "New York"     .01610613954600863
18549 "New York"   -.004230721999098727
18550 "New York"  -.0006487704591446316
18553 "New York"    .009640780359792012
18554 "New York"   -.022760312963322166
18555 "New York"    .011578625541428224
18556 "New York"   -.000482307939279464
18557 "New York"    .002210323149037938
18560 "New York"    .006063948063432866
18561 "New York"    .005846108173606016
18562 "New York"   -.004159989588867662
18563 "New York"  -.0027827162926475847
18564 "New York"   .0068274913603560096
18567 "New York"    -.00337473546706831
18568 "New York"    .014051079509719726
18569 "New York"   .0020142223500090864
18570 "New York"    .019600917449626592
18571 "New York"    .013986024275542268
18574 "New York"    .003262642383455269
18575 "New York"    -.01529693110102427
18576 "New York"    .006168071230246325
18577 "New York"   -.004247709353184027
18578 "New York"   -.011872850325028507
18581 "New York"   .0010183439224361321
18582 "New York"   -.021349621131159967
18583 "New York"   .0023037180347713613
18584 "New York"    .015266349170011498
18585 "New York"    .001335715793099815
18588 "New York" -.00047193222187249173
18589 "New York"   -.009042660092405465
18590 "New York"    .018931539637607433
18592 "New York"   -.004240148207229081
18595 "New York"   -.002653384262382237
18596 "New York"   -.005885290077534474
18597 "New York"    .018812019528911237
18598 "New York"    .008081187532685186
18599 "New York"     .00373398326430619
18602 "New York"    .004120680034900896
18603 "New York"    .003400790571900777
18604 "New York"   .0024737032262913627
18605 "New York"    .005746661046976639
18606 "New York"    .006205812382142343
18609 "New York"  -.0021001614109921836
18610 "New York"    .002165714197808426
18611 "New York"   -.004957533764823823
18612 "New York"    .005602185287633675
18613 "New York"    .002531867655203383
18616 "New York"   .0014361551457344071
18617 "New York"    .008968520421782344
18618 "New York"   .0021904466177861477
18619 "New York"   -.001119769428844882
18623 "New York"   .0017249050087448728
18624 "New York"  -.0021318418289785214
18625 "New York"   .0015788437349904517
18626 "New York"  -.0007556081080202844
18627 "New York"   .0004164110688651806
end
format %td date
tempfile returns
save `returns'

use `returns', clear

merge 1:1 city date using `scores'

//  IDENTIFY RUNS MARKED BY A NON-MISSING SCORE
//  AND ALL SUBSEQUENT DATES WITH A MISSING SCORE
by city (date), sort: gen byte spell_num = sum(missing(ret) != missing(ret[_n-1]))

//  CALCULATE SUM OF SCORES OVER EACH SPELL
by city spell_num (date), sort: egen total_score = total(score)
replace total_score = score if !missing(ret)

//  NOW MATCH WITH PRECEDING
by city (date), sort: gen match_score = total_score[_n-1] if !missing(ret)

[Edward Wu]

Thank you both! I have an idea on how to do it now.