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:


Code:
* 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


Code:
* 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