Complicated Hierarchical Data: Reshaping or Looping?

[paladin_av]

Hello Stata Wizards, hope one of you can help me with my problem.

I have hierarchical data that is a little complicated:


I want to try and reshape.

Office_ID and Branch names are corresponding. Employee_ID is unique for every employee. The Account_ID often has multiple entries for employees, because though the value in itself is unique, every time there is a change in status, there is a new entry with Date, Old_Status and New_Status.

I want to change the data into wide form so that for every Account ID there is a single row, without losing any of the data.
So that data would look something like this:


Please let me know if you need me to clarify.

 

[bukharin]

My advice is not to do this. You already have your data in the form that's best suited to most of Stata's commands. If there's something you want to do that you think requires wide format, try asking for help on that since I suspect there's a better solution than reshaping the data to wide format.

That said, if you really want to do it you'll need to create a sequence number for each entry:

bysort Office_ID Branch_Name Employee_ID Account_ID (Status_Change): gen sequno=_n
reshape wide Old_Status New_Status, i(Office_ID Branch_Name Employee_ID Account_ID) j(sequno)

 

[paladin_av]

Thank you!! It worked.

You're right about keeping it in the format that it is for stata, but I need to merge this data set with another one that has Account_ID as a unique identifier and this is the only way I could think of doing it.

Thanks!