New addition in patent?

[River Huang]

Dear All, Consider the following dataset,

clear
input str12 symbol float year str3 ipc
"000001" 2010 "G06"
"000001" 2010 "G06"
"000001" 2010 "G07"
"000001" 2013 "H04"
"000001" 2013 "G06"
"000001" 2013 "G07"
"000001" 2013 "H04"
"000001" 2013 "G06"
"000001" 2013 "G06"
"000001" 2013 "G06"
"000001" 2016 "G06"
"000001" 2016 "G06"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000001" 2017 "H04"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000002" 2002 "E04"
"000002" 2002 "E04"
"000002" 2004 "E06"
"000002" 2004 "A47"
"000002" 2004 "E04"
"000002" 2004 "E06"
"000002" 2004 "E06"
"000002" 2004 "A47"
end

I wish to count the new addition of patent ("ipc") for each company ("symbol") and each year, after the starting year of the observations (each company has different starting years). For example, the starting year of "symbol"="000001" is 2010. There are two types of patents, i.e., G06 and G07. After 2010, you can see the year is 2013, with patents G06, G07, and H04. Thus, a "wanted" variable takes 1 in year 2013 for "symbol"="000001". For the same company, in year 2016, G06 has appeared in the previous years (2010, 2013). Thus, there is no new addition and "wanted" variable = 0. Similarly, wanted = 0 in year 2017 since G06 and H04 has appeared in previous years (2010, 2013, and 2016). Any suggestions? Thanks.

[Øyvind Snilsberg]

egen tag = tag(symbol ipc)
bysort symbol year: egen wanted = total(tag)
bysort symbol (year): replace wanted = 0 if year == year[1]

[Nick Cox]

This may help. The occurrence of duplicates in the data example is a minor distraction.

clear
input str12 symbol float year str3 ipc
"000001" 2010 "G06"
"000001" 2010 "G06"
"000001" 2010 "G07"
"000001" 2013 "H04"
"000001" 2013 "G06"
"000001" 2013 "G07"
"000001" 2013 "H04"
"000001" 2013 "G06"
"000001" 2013 "G06"
"000001" 2013 "G06"
"000001" 2016 "G06"
"000001" 2016 "G06"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000001" 2017 "H04"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000002" 2002 "E04"
"000002" 2002 "E04"
"000002" 2004 "E06"
"000002" 2004 "A47"
"000002" 2004 "E04"
"000002" 2004 "E06"
"000002" 2004 "E06"
"000002" 2004 "A47"
end

duplicates drop 

bysort symbol (year) : gen year1 = year[1]
bysort symbol ipc (year) : gen wanted = (year == year[1]) & (year[1] != year1)
bysort symbol year (wanted) : replace wanted = wanted[_N]
sort symbol year ipc
list, sepby(symbol year)


     +--------------------------------------+
     | symbol   year   ipc   year1   wanted |
     |--------------------------------------|
  1. | 000001   2010   G06    2010        0 |
  2. | 000001   2010   G07    2010        0 |
     |--------------------------------------|
  3. | 000001   2013   G06    2010        1 |
  4. | 000001   2013   G07    2010        1 |
  5. | 000001   2013   H04    2010        1 |
     |--------------------------------------|
  6. | 000001   2016   G06    2010        0 |
     |--------------------------------------|
  7. | 000001   2017   G06    2010        0 |
  8. | 000001   2017   H04    2010        0 |
     |--------------------------------------|
  9. | 000002   2002   E04    2002        0 |
     |--------------------------------------|
 10. | 000002   2004   A47    2002        1 |
 11. | 000002   2004   E04    2002        1 |
 12. | 000002   2004   E06    2002        1 |
     +--------------------------------------+

[River Huang]

Dear Øyvind, Thanks a lot for this helpful suggestion.

[River Huang]

Dear Nick, Thanks a lot for this helpful suggestion. However, for symbol = 000002 and year = 2004, wanted should be 2, not 1.

[Nick Cox]

clear
input str12 symbol float year str3 ipc
"000001" 2010 "G06"
"000001" 2010 "G06"
"000001" 2010 "G07"
"000001" 2013 "H04"
"000001" 2013 "G06"
"000001" 2013 "G07"
"000001" 2013 "H04"
"000001" 2013 "G06"
"000001" 2013 "G06"
"000001" 2013 "G06"
"000001" 2016 "G06"
"000001" 2016 "G06"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000001" 2017 "H04"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000001" 2017 "G06"
"000002" 2002 "E04"
"000002" 2002 "E04"
"000002" 2004 "E06"
"000002" 2004 "A47"
"000002" 2004 "E04"
"000002" 2004 "E06"
"000002" 2004 "E06"
"000002" 2004 "A47"
end

duplicates drop 

bysort symbol (year) : gen year1 = year[1]
bysort symbol ipc (year): gen tag = _n == 1 & year > year1 
sort symbol year ipc 
egen wanted = total(tag), by(symbol year)

list, sepby(symbol year)

     +--------------------------------------------+
     | symbol   year   ipc   year1   tag   wanted |
     |--------------------------------------------|
  1. | 000001   2010   G06    2010     0        0 |
  2. | 000001   2010   G07    2010     0        0 |
     |--------------------------------------------|
  3. | 000001   2013   G06    2010     0        1 |
  4. | 000001   2013   G07    2010     0        1 |
  5. | 000001   2013   H04    2010     1        1 |
     |--------------------------------------------|
  6. | 000001   2016   G06    2010     0        0 |
     |--------------------------------------------|
  7. | 000001   2017   G06    2010     0        0 |
  8. | 000001   2017   H04    2010     0        0 |
     |--------------------------------------------|
  9. | 000002   2002   E04    2002     0        0 |
     |--------------------------------------------|
 10. | 000002   2004   A47    2002     1        2 |
 11. | 000002   2004   E04    2002     0        2 |
 12. | 000002   2004   E06    2002     1        2 |
     +--------------------------------------------+

[River Huang]

Dear Nick, Got it and thanks again.