Combining duplicate names into one

[Roel Verkuijlen]

Hello everyone,

I have a dataset containing data about Board Members and it is formatted like this:

Name	Start Date	End Date	Role Name	Ticker
Mr. Jan Appels	21-03-2010	31-12-2010	Board member	AED
Mr. Jan Appels	01-01-2011	20-11-2018	CEO	AED

My question is: Is there a way to combine these two observations into one which contains the first start date and the latest end date based on Name and Ticker, where Role Name could be dropped as it is not a useful variable for my research. So the data would then look like:

Name	Start Date	End Date	Ticker
Mr. Jan Appels	21-03-2010	20-11-2018	AED

I've tried several things but without success, hopefully, someone can help me.
Thanks in advance.

Regards,
Roel

[Nick Cox]

That would be a reshape wide but often repeated advice here is that your layout is better for most Stata purposes.

[William Lisowski]

I think perhaps this does what you want, although a warning is necessary. If a Board Member has a gap in service at a given Ticker, this will make it appear that the service is continuous, rather than two separate spells.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 name str10(startdate enddate) str12 rolename str3 ticker
"Mr. Jan Appels" "21-03-2010" "31-12-2010" "Board member" "AED"
"Mr. Jan Appels" "01-01-2011" "20-11-2018" "CEO"          "AED"
end

* convert string dates to SIF dates
rename (startdate enddate) (sd ed)
generate startdate = daily(sd,"DMY")
generate enddate   = daily(ed,"DMY")
format %td startdate enddate

* collapse to a single observation for each name/ticker combination
collapse (min) startdate (max) enddate, by(name ticker)

list

. list

     +-------------------------------------------------+
     |           name   ticker   startdate     enddate |
     |-------------------------------------------------|
  1. | Mr. Jan Appels      AED   21mar2010   20nov2018 |
     +-------------------------------------------------+

[Roel Verkuijlen]

Thanks for your replies! William, your suggestion seemed to work out for my dataset, thanks again!