st: How to transfer strings in different rows of a cell into different observations

[XIAOFEI XING <mike.xf.xing@gmail.com>]

Dear Statalist,

I have a following format excel file:

Number        ID                Assignment
1                   A A                  AS1 E2
                       B                    AS2 A3
                       C                   AS3
2                    A                    AS6
                     D F                  AS2
                       F                   AS10
3                  W W                AS9

Take the observation Number 1 as an example, string "A A", "B" and "C" are in the different rows of a same cell of a excel file. Similarly, string "AS1 E2", "AS2 A3" and "AS3" are also in the same cell.

I want to use Stata to transfer different rows of strings into different observations, showing as following:

Number        ID                Assignment
1                   A A                  AS1 E2
1                     B                   AS2 A3
1                     C                    AS3
2                     A                    AS6
2                   D F                  AS2
2                     F                    AS10
3                  W W                 AS9

How can I do it?

Thanks a lot!

Xiaofei Xing
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/