Merge and Split Data

[Yujin An]

I need some help on how to merge rows in STATA, I would like to merge from:

| Individual | Year | City |

| John Doe | 2018 | NYC |

| John Doe | 2019 | NYC |

| John Doe | 2020 | NYC |

| John Doe | 2019 | LA |

| John Doe | 2020 | LA |

| Kate Smith | 2020 | NYC |

| Kate Smith | 2021 | NYC |

to:

| Individual | Year 1 | Year 2 | City |

| John Doe | 2018 | 2020 | NYC |

| John Doe | 2011 | 2020 | LA |

| Kate Smith | 2020 | 2021. | NYC |

How should I do it?

Thank uuu

[Andrew Musau]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 individual int year str5 city
" John Doe "   2018 " NYC "
" John Doe "   2019 " NYC "
" John Doe "   2020 " NYC "
" John Doe "   2019 " LA " 
" John Doe "   2020 " LA " 
" Kate Smith " 2020 " NYC "
" Kate Smith " 2021 " NYC "
end

bys individual city  (year): gen which=_n
reshape wide year, i(individual city) j(which)

Res.:

. l

     +----------------------------------------------+
     |   individual    city   year1   year2   year3 |
     |----------------------------------------------|
  1. |    John Doe      LA     2019    2020       . |
  2. |    John Doe     NYC     2018    2019    2020 |
  3. |  Kate Smith     NYC     2020    2021       . |
     +----------------------------------------------+

[Yujin An]

At the 'input str12 individual int year str5 city' step, I am keep getting variable already defined.

What should I do in this case if the dataset already defined all the variables?

Thank you !!

[Nick Cox]

You don't need to type in the data example. You should start with

 
 bys individual city  (year): gen which=_n

where you will need to change variable names if they are not individual city year.

[Yujin An]

Thank you :D