Re: st: Difficult merging process

["Michael N. Mitchell" <Michael.Norman.Mitchell@gmail.com>]

Dear Nathan

  This is a very thorny problem!

  My first thought is to try and focus on the dates as a way of matching, matching on the 
"week" of the whether event to "week" of pregnancy...

  1) For the weather dataset, convert the "date" of the event into the "week" of the 
event (using the -wofd()- function). Call this variable "dateweek".

  2) For the pregnancy dataset, make one record for every week of pregnancy per person 
(for a 9 month gestation, each person would have 36 records, one for each week of 
pregnancy... each record would be identified by the person id and the "dateweek" for each 
week of pregnancy.)  This step would involve computing the number of weeks of gestation, 
using the "expand" command to make the multiple records per person, and then a -by id: 
generate- to compute the week number of pregnancy for the multiple weeks of pregnancy.

  3) match merge the "weather" dataset to the "pregnancy" dataset on "dateweek", keeping 
just the matches. The resulting dataset contains the weeks of pregnancy with a weather event.

  4) Compute the distance from the mother to the weather event. Eliminate events that are 
too many miles away.

  5) There may be multiple records per mother per weather event. Use collapse to make one 
record per mother.

  Others might have better thoughts. I hope this helps.

Michael N. Mitchell
Data Management Using Stata      - http://www.stata.com/bookstore/dmus.html
A Visual Guide to Stata Graphics - http://www.stata.com/bookstore/vgsg.html
Stata tidbit of the week         - http://www.MichaelNormanMitchell.com



On 2010-12-20 5.46 PM, Nathan Hutto wrote:
> Hi all,
>
> I am attempting to merge two data sets in a way that is new to am and
> am having trouble figuring out how to do so. One data set contains
> geo-coded birth certificates and the other contains weather events. I
> want to determine whether a mother was exposed to a weather event
> during the course of her pregnancy. I have the birth date and
> gestational length, so I can determine the dates of gestation by
> subtracting one from the other. I also have the latitude and
> longitude, address, city, and state of each pregnancy and weather
> event. For this case, exposure to a weather event would be defined as
> being pregnant when a weather event occurred in close proximity.
>
> I'm ok with over-merging a little bit; I can determine the exact
> exposure by using one of Stata's length commands that can calculate
> distance with latitude and longitude. But given that many people in my
> data are exposed to a number of weather events, I'd like to whittle
> down the amount of false positives.
>
> Any thoughts on this?
>
> Thank you,
> Nathan
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/statalist/faq
> *   http://www.ats.ucla.edu/stat/stata/
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/