rearranging dataset from role based to date based

[Casper Menge]

Hi all,
I have ran into an issue. My dataset has observations for individuals and their roles within an organization. It is organised as follows, Person X had role Y strarting on Date 1 until date 2. So for example: John, Accountant, 31/01/2015, 25/03/2023. However, I would like my dataset to be as follows 31/01/2015, John, Accountant. 1/02/2015, John, Accountant, 2/02/2015, John Accountant.........
Would this be possible and if so How?

[Clyde Schechter]

* Example generated by -dataex-. For more info, type help dataex
clear
input str4 name str11 role float(start end)
"John" " Accountant" 20119 23094
end
format %td start
format %td end

gen n_days = end - start + 1
expand n_days
by name role start end, sort: gen date = start + _n - 1
format date %td
drop n_days start end
sort name date

In the future, please use the -dataex- command to show example data on this Forum, as I have done in this response. Your description, though well intentioned, does not sufficiently describe the data set to support writing code that solves your problem. I have made some educated guesses about undisclosed aspects of your data, but if I have guessed wrong the code shown above will not work, and we will have both wasted some time. The -dataex- command provides a complete and faithful replica of the example data, including the metadata needed to write code correctly, and is immediately importable to Stata. No description, -list- output, tables or screenshot can do that.

If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

If the code shown above does not work in your data set, please post back with example data, using -dataex-, and I will try to troubleshoot the problem.