How to expand the variable within group?

[Grace FM]

Here is what I am trying to do I want to make each one of the observations in the following dataset from left to right. Namely, within the group, I want each firm to have an observation with the rest of the group variables. How can I achieve this?

[Nick Cox]

That looks like an image of a worksheet in a spreadsheet. Please follow FAQ Advice #12 to give a Stata data example people can use.

[Grace FM]

Sorry Nick, I attached the stata data here.

My original data looks like this:

clear
input str12 (firmid location1 location2) float sic
"25625"    "howard" "MD" 100
"73176"     "yolo"    "CA" 100
"89821"     "yolo"    "CA" 100
"226082" "newyork" "NY" 200
"53642"     "kern" "CA" 200
"462962" "lee"    "AL" 200
"27018"     "losangeles" "CA" 200
"361041" "sanfrancisco"    "CA" 300
"141709" "oklahoma"    "OK" 300
"126246" "yellowstone"    "MT" 300
"122140" "sanfrancisco"    "CA" 300
"277854" "sandiego"    "CA" 300
"307235" "fairfield" "CT" 300
"427294" "allegan" "MI" 300
end

And I want the output to look like the following table. Essentially, I want to expand the observation within group. Namely, I want each one of the observations could have a pair with all the other observations in the group.

clear
input str12 (firmid1 location1 location2) float sic1 str12 (firmid2 location12 location22) float sic2
"25625"    "howard" "MD" 100 "73176" "yolo" "CA" 100
"25625"    "howard" "MD" 100 "89821" "yolo" "CA" 100
"73176"     "yolo"    "CA" 100  "25625" "howard" "MD" 100
"73176"     "yolo"    "CA" 100  "89821" "yolo" "CA" 100
"89821"     "yolo"    "CA" 100 "25625" "howard" "MD" 100
"89821"     "yolo"    "CA" 100 "73176" "yolo"    "CA" 100
"226082" "newyork" "NY" 200 "53642"     "kern" "CA" 200
"226082" "newyork" "NY" 200 "462962" "lee"    "AL" 200
"226082" "newyork" "NY" 200 "27018"     "losangeles" "CA" 200
"53642"     "kern" "CA" 200 "226082" "newyork" "NY" 200
"53642"     "kern" "CA" 200 "462962" "lee"    "AL" 200
"53642"     "kern" "CA" 200 "27018" "losangeles" "CA" 200
"462962" "lee"    "AL" 200 "226082" "newyork" "NY" 200
"462962" "lee"    "AL" 200 "226082" "newyork" "NY" 200
"462962" "lee"    "AL" 200 "27018" "losangeles" "CA" 200
end

Thank you so much for your help.

[Nick Cox]

Thanks for the data example. What defines a group?

[Grace FM]

Within the same sic group. I want each of the firm to pair with another firm, along with each associated location information.

Thanks.

[Nick Cox]

OK, so it seems that you don't need sic1 and sic2 because they have to be the same. This seems to be quite a common problem that I never need to do myself, so having elicited clarification of the problem I will sit back and hope that others can solve it.

[Andrew Musau]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12(firmid location1 location2) float sic
"25625"  "howard"       "MD" 100
"73176"  "yolo"         "CA" 100
"89821"  "yolo"         "CA" 100
"226082" "newyork"      "NY" 200
"53642"  "kern"         "CA" 200
"462962" "lee"          "AL" 200
"27018"  "losangeles"   "CA" 200
"361041" "sanfrancisco" "CA" 300
"141709" "oklahoma"     "OK" 300
"126246" "yellowstone"  "MT" 300
"122140" "sanfrancisco" "CA" 300
"277854" "sandiego"     "CA" 300
"307235" "fairfield"    "CT" 300
"427294" "allegan"      "MI" 300
end

preserve
rename * *_2
gen sic= sic_2
tempfile 2
save `2'
restore
joinby sic using `2'
bys sic firmid location1: drop if (firmid==firmid_2)

Res.:

. l, sepby(sic)

     +-------------------------------------------------------------------------------------+
     | firmid      location1   locat~n2   sic   firmid_2    location1_2   loca~2_2   sic_2 |
     |-------------------------------------------------------------------------------------|
  1. |  25625         howard         MD   100      73176           yolo         CA     100 |
  2. |  25625         howard         MD   100      89821           yolo         CA     100 |
  3. |  73176           yolo         CA   100      25625         howard         MD     100 |
  4. |  73176           yolo         CA   100      89821           yolo         CA     100 |
  5. |  89821           yolo         CA   100      25625         howard         MD     100 |
  6. |  89821           yolo         CA   100      73176           yolo         CA     100 |
     |-------------------------------------------------------------------------------------|
  7. | 226082        newyork         NY   200      53642           kern         CA     200 |
  8. | 226082        newyork         NY   200      27018     losangeles         CA     200 |
  9. | 226082        newyork         NY   200     462962            lee         AL     200 |
 10. |  27018     losangeles         CA   200     226082        newyork         NY     200 |
 11. |  27018     losangeles         CA   200     462962            lee         AL     200 |
 12. |  27018     losangeles         CA   200      53642           kern         CA     200 |
 13. | 462962            lee         AL   200      27018     losangeles         CA     200 |
 14. | 462962            lee         AL   200     226082        newyork         NY     200 |
 15. | 462962            lee         AL   200      53642           kern         CA     200 |
 16. |  53642           kern         CA   200      27018     losangeles         CA     200 |
 17. |  53642           kern         CA   200     462962            lee         AL     200 |
 18. |  53642           kern         CA   200     226082        newyork         NY     200 |
     |-------------------------------------------------------------------------------------|
 19. | 122140   sanfrancisco         CA   300     307235      fairfield         CT     300 |
 20. | 122140   sanfrancisco         CA   300     277854       sandiego         CA     300 |
 21. | 122140   sanfrancisco         CA   300     141709       oklahoma         OK     300 |
 22. | 122140   sanfrancisco         CA   300     361041   sanfrancisco         CA     300 |
 23. | 122140   sanfrancisco         CA   300     126246    yellowstone         MT     300 |
 24. | 122140   sanfrancisco         CA   300     427294        allegan         MI     300 |
 25. | 126246    yellowstone         MT   300     122140   sanfrancisco         CA     300 |
 26. | 126246    yellowstone         MT   300     277854       sandiego         CA     300 |
 27. | 126246    yellowstone         MT   300     427294        allegan         MI     300 |
 28. | 126246    yellowstone         MT   300     141709       oklahoma         OK     300 |
 29. | 126246    yellowstone         MT   300     361041   sanfrancisco         CA     300 |
 30. | 126246    yellowstone         MT   300     307235      fairfield         CT     300 |
 31. | 141709       oklahoma         OK   300     122140   sanfrancisco         CA     300 |
 32. | 141709       oklahoma         OK   300     277854       sandiego         CA     300 |
 33. | 141709       oklahoma         OK   300     427294        allegan         MI     300 |
 34. | 141709       oklahoma         OK   300     126246    yellowstone         MT     300 |
 35. | 141709       oklahoma         OK   300     307235      fairfield         CT     300 |
 36. | 141709       oklahoma         OK   300     361041   sanfrancisco         CA     300 |
 37. | 277854       sandiego         CA   300     141709       oklahoma         OK     300 |
 38. | 277854       sandiego         CA   300     427294        allegan         MI     300 |
 39. | 277854       sandiego         CA   300     361041   sanfrancisco         CA     300 |
 40. | 277854       sandiego         CA   300     126246    yellowstone         MT     300 |
 41. | 277854       sandiego         CA   300     307235      fairfield         CT     300 |
 42. | 277854       sandiego         CA   300     122140   sanfrancisco         CA     300 |
 43. | 307235      fairfield         CT   300     141709       oklahoma         OK     300 |
 44. | 307235      fairfield         CT   300     277854       sandiego         CA     300 |
 45. | 307235      fairfield         CT   300     427294        allegan         MI     300 |
 46. | 307235      fairfield         CT   300     361041   sanfrancisco         CA     300 |
 47. | 307235      fairfield         CT   300     122140   sanfrancisco         CA     300 |
 48. | 307235      fairfield         CT   300     126246    yellowstone         MT     300 |
 49. | 361041   sanfrancisco         CA   300     307235      fairfield         CT     300 |
 50. | 361041   sanfrancisco         CA   300     122140   sanfrancisco         CA     300 |
 51. | 361041   sanfrancisco         CA   300     277854       sandiego         CA     300 |
 52. | 361041   sanfrancisco         CA   300     126246    yellowstone         MT     300 |
 53. | 361041   sanfrancisco         CA   300     141709       oklahoma         OK     300 |
 54. | 361041   sanfrancisco         CA   300     427294        allegan         MI     300 |
 55. | 427294        allegan         MI   300     126246    yellowstone         MT     300 |
 56. | 427294        allegan         MI   300     122140   sanfrancisco         CA     300 |
 57. | 427294        allegan         MI   300     361041   sanfrancisco         CA     300 |
 58. | 427294        allegan         MI   300     307235      fairfield         CT     300 |
 59. | 427294        allegan         MI   300     277854       sandiego         CA     300 |
 60. | 427294        allegan         MI   300     141709       oklahoma         OK     300 |
     +-------------------------------------------------------------------------------------+

.

[Grace FM]

Thank you so much Nick and Andrew. This is exactly what I need.

Much appreciated.