Basic question on transforming dataset into new format

[Nils Dubois]

Dear all,

I have a dataset from BoardEx which contains data in the following format:

Company-ID | Director-ID | start date | end date

I want to ultimately create a variable which contains magnitude of board turnover, for which I will need to transform this data into firm-year observations like so:

Company-ID | Year | # outgoing directors | # incoming directors

How would I go about transforming the existing dataset into the new format?

Thanks in advance!

[Sofia Hernandez]

Hi, please provide a working example of the data (using dataex, see FAQ) so we can write the code. I would begin by extracting the Year from the start and end date, and for each company ID create variables "Start_Year_1, Start_Year_2,....End_Year_3..., etc". Then, I'd pivot the data from wide to long format such that the resulting dataset will be on the Company ID - Year format (multiple Year values per Company ID). The calculation of # ingoing and # outgoing would then be based on how many times Company ID appears when Start_Year_1==1. I cannot explain any further without a data example.