I have a dataset that contains the following sample observations. Var1 has ID's separated by dashes. Var2 is date and Var3 is location.
Var1 Var2 Var3
AB6772-CD5885-ABCD6194-XY7199---- 09/14/2010 London
PQ5928-SP352-BZ5752----- 09/16/2010 New York
Each string in Var1 has from 1 to 8 ID's. In this example, there are 4 ID's in the first obs and 3 in the second. The dashes at the end of the string mean that that there are no more ID's for that obs.
I would like to extract each ID and transpose the file as follows:
ID Var2 Var3
AB6772 09/14/2010 London
CD5885 09/14/2010 London
ABCD6194 09/14/2010 London
XY7199 09/14/2010 London
PQ5928 09/16/2010 New York
SP352 09/16/2010 New York
BZ5752 09/16/2010 New York
I am looking for a simple way to achieve this. Any suggestions?
... View more