Hi all,

So as usual, I'm having a hard time describing what I'm trying to do, which I believe is why googling has been ineffective for me. So let me use some examples.

I have a primary dataset that look like this:

Table 1
Address State Year PolicyType1 PolicyType2 PolicyType2 Detail PolicyType3 PolicyType3 Detail
12 road CA 2006 . . . . .
234 st RI 2012 . . . . .
3534 ave RI 2007 . . . . .
125 road WV 2016 . . . . .
534 street CA 2014 . . . . .
2423 lane OH 2009 . . . . .
I also have a secondary dataset that looks like this


Table 2
PolicyType State Year PolicyDetail2 PolicyDetail3
1 CA 2008 . .
1 CA 2005 . .
2 RI 2009 Abc .
3 OH 2001 . 50
What I want is to combine these datasets so that each observation in table 1 gets a value added to the policy (counting number of policies) if it is in or after the year of the policy and in the state. I also want the corresponding policy detail to be added. I know that's a bit confusing, so in the example above, I would want table 1 to be:


Table 1
Address State Year PolicyType1 PolicyType2 PolicyType2 Detail PolicyType3 PolicyType3 Detail
12 road CA 2006 1 . . . .
234 st RI 2012 . 1 Abc . .
3534 ave RI 2007 . . . . .
125 road WV 2016 . . . . .
534 street CA 2014 2 . . . .
2423 lane OH 2009 . . . 1 50
Hopefully, this makes some sense, I'm having a bunch of trouble trying to google this, so any help or guidance would be appreciated.

Best,
Max