I would like to merge two databases but I can not merge firms with two ISIN

[Mo Hos]

I use a database in which firms have sometimes two International Securities Identification Number (ISIN). I would like to merge two databases but I can not merge firms with two ISIN (I use the ISIN to merge the two databases). Is there any way I can convert two ISINs next to each other into vertically sperate cells (from horizontal to vertical). For example:

ISIN___________________|_Number of employees | number of executives |
GB32153453 GB34523454 | 12365 _____________| 20

I would like them to be:

ISIN_______ |_Number of employees | number of executives |
GB32153453 | 12365 _____________| 20
GB34523454 | 12365 _____________| 20

I would like to use a command in STATA to do this for me.

[William Lisowski]

Perhaps this will start you in a useful direction.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str21 ISIN int emp byte exec
"GB32153453 GB34523454" 12365 20
"GB12345678"              345 10
end

split ISIN
ds
drop ISIN
generate seq = _n
reshape long ISIN, i(seq) j(j)
drop if ISIN==""
ds
drop seq j
order ISIN
list, noobs

. split ISIN
variables created as string: 
ISIN1  ISIN2

. ds
ISIN   emp    exec   ISIN1  ISIN2

. drop ISIN

. generate seq = _n

. reshape long ISIN, i(seq) j(j)
(note: j = 1 2)

Data                               wide   ->   long
-----------------------------------------------------------------------------
Number of obs.                        2   ->       4
Number of variables                   5   ->       5
j variable (2 values)                     ->   j
xij variables:
                            ISIN1 ISIN2   ->   ISIN
-----------------------------------------------------------------------------

. drop if ISIN==""
(1 observation deleted)

. ds
seq   j     emp   exec  ISIN

. drop seq j

. order ISIN

. list, noobs

  +---------------------------+
  |       ISIN     emp   exec |
  |---------------------------|
  | GB32153453   12365     20 |
  | GB34523454   12365     20 |
  | GB12345678     345     10 |
  +---------------------------+

.

With this said, some advice for future posts. Please take a few moments to review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question. It's particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using code delimiters [CODE] and [/CODE], and to use the dataex command to provide sample data, as described in section 12 of the FAQ.

And a trivial note: the name of the program you are using is typed "Stata" rather than "STATA", although I will agree that the trademark displayed at the start of the Results window has an upper case appearance at a casual glance: only the letters "a" are obviously lower case.

  ___  ____  ____  ____  ____ (R)
 /__    /   ____/   /   ____/
___/   /   /___/   /   /___/

[Mo Hos]

Thank you very much.

I have a small problem. There are a comma and space between the two ISIN:
GB32153453, GB34523454

How can I delete both comma and space?

[William Lisowski]

I didn't notice the comma and the space in your example in post #1.

Looking at the output of help split tells us about the parse option.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str24 ISIN int emp byte exec
"GB32153453 GB34523454"  12365 20
"GB12345678"               345 10
"GB87654321, GB98765432"   666 42
end

split ISIN, parse(" " ", ")
ds
drop ISIN
generate seq = _n
reshape long ISIN, i(seq) j(j)
drop if ISIN==""
ds
drop seq j
order ISIN
list, noobs

will produce

. list, noobs

  +---------------------------+
  |       ISIN     emp   exec |
  |---------------------------|
  | GB32153453   12365     20 |
  | GB34523454   12365     20 |
  | GB12345678     345     10 |
  | GB87654321     666     42 |
  | GB98765432     666     42 |
  +---------------------------+

[Mo Hos]

Thank you very much. Your recommendation is highly appreciated.