File 1 (Census) holds child population estimates for every state, for various years. It has variables called State, ChildPopulation, and CensusDate. CensusDate has values like 7/1/2011, 7/1/2011, 7/2/2012, etc.
File 2 (MyData) has a lot of data for each State, including two variables, DtStart and DtEnd. DtStart and DtEnd always span one year, like 4/1/2011 to 3/31/2012.
State is numeric in both files.
I would like to merge the value of ChildPopulation from File 1 (Census) into File 2 (matching on State, obviously), but I only want the child populations where CensusDate (in File 1) is between DtStart and DtEnd (in File 2).
Any suggestions about doing this? I wonder if there is a more elegant solution to my current approach, which merges DtStart and DtEnd into File 1 (Census), flags the records where CensusDate is between DtStart and DtEnd, and then merges the flagged records back into File 2 (MyData).
Thank you for any suggestions.