Splitting data from one cell

[Feli Huf]

Hi everyone,

I am currently working with patent data. Every row contains information on the applicants ID, year of patent application, as well as backward citations of a given patent. The backward cited patents are listed below each other within one cell. For example:

US5564332A
US5692382A
US5760065A
....

I would like to split the backward cited patent IDs up and create a new column for each "row" within the cell so that I only have one patent ID per cell to be able to merge the data set with another dataset.

I hope, it is clear what I mean - feel free to ask questions of course.

I would be very grateful for any help!!

Thanks a lot in advance,
Feli

[Ken Chui]

If you can use the dataex command to provide a couple rows of example data (as how it appears in Stata), it may be more likely to get an answer. See FAQ part 12.

[George Ford]

Given that lack of a dataex, I'll take a shot in the dark.

bys patentid: g backnum = _n

This will give you a running indicator of the backward citations.

keep patentid backwardsid backnum

you can then drop all rows in a loop (preserve/restore) that don't = 1, 2, 3, .... and save the data as backnum`i'.

forv i = 1/maxbacknum {
    preserve
        keep if backnum == `i'
        rename backwardsid backwardsid`i'
        drop backnum
        save backnum`i', replace
    restore
}

Then

use masterdata, clear
forv i = 1/maxbacknum {
     joinby patentid using backnum`i' , unmatched(master) _merge(_merge`i')
}

[Feli Huf]

Thank you so much for your help. George, your solution worked for me, thanks!!

Have a good day!