Merge data where date in File 1 is between 2 dates in File 2

Moderators: statman, Analyst Techy, andris, Fierce, GerineL, Smash

0101
Posts: 52
Joined: Fri Mar 04, 2011 9:42 pm

Merge data where date in File 1 is between 2 dates in File 2

Postby 0101 » Wed Mar 25, 2015 1:59 am

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.
GerineL
Moderator
Posts: 1477
Joined: Tue Jun 10, 2008 4:50 pm

Re: Merge data where date in File 1 is between 2 dates in Fi

Postby GerineL » Fri Mar 27, 2015 3:49 pm

What if you do it the other way around? so merge all census data with mydata, and delete the ones in which censusdate is not in the mydata file?
Saves one merge.
Otherwise I would not know either.

Who is online

Users browsing this forum: No registered users and 1 guest

cron