Merging Multiple Rows to Multiple Rows

[Netane Ben-Porath]

Hello,
I have two data sets I would like to merge.

The first (main) one looks like this:

Id	State
1	1
2	1
3	1
4	2
5	2
6	3

The second one looks like this:

State	Year
1	1960
1	1962
2	1970
2	1974
2	1978
3	1960

What I would like to do is to merge each ID in the main data all the years in the second data, so each year would appear in a different row. So I would like finally to have:

Id	State	Year
1	1	1960
1	1	1962
2	1	1960
2	1	1962
3	1	1960
3	1	1962
4	2	1970
4	2	1974
4	2	1978
5	2	1970
5	2	1974
5	2	1978
6	3	1960

How could this be achieved? I should mention both datasets contain additional variables I would like to merge similiarly.
Thank you,
Netanel

[Andrew Musau]

help joinby

* Example generated by -dataex-. For more info, type help dataex
clear
input byte state int year
1 1960
1 1962
2 1970
2 1974
2 1978
3 1960
end

tempfile state
save `state'

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(id state)
1 1
2 1
3 1
4 2
5 2
6 3
end

joinby state using `state'

Res.:

. l, sepby(id)

     +-------------------+
     | id   state   year |
     |-------------------|
  1. |  1       1   1962 |
  2. |  1       1   1960 |
     |-------------------|
  3. |  2       1   1962 |
  4. |  2       1   1960 |
     |-------------------|
  5. |  3       1   1962 |
  6. |  3       1   1960 |
     |-------------------|
  7. |  4       2   1978 |
  8. |  4       2   1970 |
  9. |  4       2   1974 |
     |-------------------|
 10. |  5       2   1978 |
 11. |  5       2   1974 |
 12. |  5       2   1970 |
     |-------------------|
 13. |  6       3   1960 |
     +-------------------+