How about using -joinby- to locate the observations where date is
between start and end and then -merge- to pick up the unmatched spells.
For example:
clear
input id str10 startd str10 endd othervars
1 "jan1,2006" "mar15,2006" 5
1 "mar16,2006" "jun1,2006" 13
1 "jun2,2006" "mar1,2007" 7
2 "feb7,2006" "mar3,2006" 6
2 "jun4,2006" "sep5,2006" 6
end
gen start = date(startd, "md19y")
gen end = date(endd, "md19y")
format start %d
format end %d
drop startd endd
sort id start
tempfile temp1
save `temp1'
clear
input id str10 date otherstuff
1 "feb1,2006" 7
1 "feb2,2007" 8
2 "mar3,2006" 3
end
gen date2 = date(date, "md19y")
format date2 %d
joinby id using `temp1'
gen tag = start if date2 <= end & date2 >=start
format tag %d
keep if tag != .
keep id date2 otherstuff tag
rename tag start
sort id start
merge id start using `temp1',
sort id start
l
Scott
----- Original Message -----
From: Amanda Kowalski <[email protected]>
Date: Wednesday, July 12, 2006 9:39 am
Subject: st: merging spell data to episode data
To: [email protected]
> Statalist,
>
> I am trying to merge spell data to episode data. For example, my
> spell data
> looks something like this (with dates formatted as dates in Stata):
>
> id startd endd othervars
> 1 jan1,2006 mar15,2006 5
> 1 mar16,2006 jun1,2006 13
> 1 jun2,2006 mar1,2007 7
> 2 feb7,2006 mar3,2006 6
> 2 jun4,2006 sep5,2006 6
>
> (None of the spells overlap, but there are gaps).
>
> And my episode data looks somthing like this:
>
> id date otherstuff
> 1 feb1,2006 7
> 1 feb2,2007 8
> 2 mar3,2006 3
>
> For each episode, I'd like to merge on the data from the spell
> that covers
> it. Ideally, there would be a merging rule that would merge by id
> if date
> is in the range specified by startd and endd, but I can't find any
> commandsthat do this.
>
> I have searched the stata help files, the internet, and the statlist
> archives. Do you have any suggestions? My inelegant ideas include:
>
> Turn the spell data into date data by making one observation for
> every date,
> retaining startd and endd with every observation, and then merge.
> Thisisn't a good plan because my dataset is very large.
>
> Use "joinby" to merge every pairwise combination of observations
> by id.
> Then, if date isn't between startd and endd, discard the observation.
> However, I'd prefer to retain unmatched spells and episodes, and this
> strategy would make it harder to locate them.
>
> Any suggestions?
>
> Thanks,
> Amanda
>
*
* For searches and help try:
* http://www.stata.com/support/faqs/res/findit.html
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
