Search codes for transnational cooperation patents

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

Faery
Posts: 3
Joined: Mon May 11, 2020 6:14 am

Search codes for transnational cooperation patents

Post by Faery » Mon May 11, 2020 1:23 pm

I want to search for patents for cooperation in 65 countries (with a list of specific countries), the specific time is 2014 to 2019,so as to establish a patent cooperation network, I want to get these data and build a 65 * 65 matrix,but I don’t know how to write the code. I hope that someone can help me look at it! The current code is

Code: Select all

select count(distinct pa1.appln_id),person_ctry_code
from tls207_pers_appln pa1
 JOIN tls206_person p1 ON p1.person_id = pa1.person_id
JOIN  tls201_appln ap1 on ap1. appln_id=pa1. appln_id
where  ap1.earliest_publn_year between '2014' and '2019'
and pa1.appln_id in 
(select pa2.appln_id from tls207_pers_appln pa2 join tls206_person p2 
ON p2.person_id = pa2.person_id where  p2.person_ctry_code in ('CN') and pa2.invt_seq_nr>0)
and pa1.appln_id in 
(select  pa3.appln_id from tls207_pers_appln pa3 join tls206_person p3 
ON p3.person_id = pa3.person_id where p3.person_ctry_code in ('AL','AF','AE','OM', 
'AZ','EG','EE', 'PK','PS','BH', 'BY','BG','BA','PL','BT','TP','RU','PH','GE','KZ', 'ME', 'KG','KH', 
'CZ','QA','KW','HR','LV','LA', 'LB','LT','RO','MV','MY','MK','MN','BD','MM','MD', 
'NP','RS','SA' ,'LK','SK','SI','TJ' ,'TH','TR','TM','BN', 'UA','UZ','SG','HU','SY','AM','YE','IQ'
,'IR','IL','IN','ID','JO','VN')  
and pa3.applt_seq_nr>0)
and applt_seq_nr>0
and  person_ctry_code in ('AL','AF','AE','OM','AZ', 'EG','EE','PK','PS','BH',
 'BY' ,'BG','BA', 'PL','BT','TP', 'RU','PH','GE','KZ','ME', 'KG','KH','CZ','QA',
 'KW','HR','LV','LA','LB','LT','RO', 'MV','MY','MK' ,'MN','BD','MM','MD','NP', 
 'RS','SA' ,'LK','SK' ,'SI','TJ','TH','TR', 'TM','BN','UA',' UZ','SG','HU','SY'
 ,'AM', 'YE','IQ','IR','IL', 'IN','ID','JO','VN') 
group by person_ctry_code
order by count(distinct pa1.appln_id)
This is the number of all patents that I think China cooperates with these countries except China
Then if you want to modify the code 64 times if you build a complete network, I find it more troublesome to see if there is a better code, thank you very much!


EPO / PATSTAT Support
Posts: 227
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Search codes for transnational cooperation patents

Post by EPO / PATSTAT Support » Tue May 12, 2020 9:07 am

Hello Faery,
is your network applicant or inventor based (or both) ?
Your query looks for Chinese inventors (or inventors/applicants) co-filing with non-Chinese applicants.
I assume you want to build some kind of matrix, what is in the columns, rows and the intersections ?
You also did not limit your sample to any patent authority; this as such is fine, but be aware that for example for patents filed in China, the EPO hardly receives any "person_ctry_code" data from China.
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Faery
Posts: 3
Joined: Mon May 11, 2020 6:14 am

Re: Search codes for transnational cooperation patents

Post by Faery » Wed May 27, 2020 3:01 am

Thank you for your reply.
I've looked up a lot of papers on your question and It's clear now.
I want to build a non-directed patent network where the nodes are 65 specific countries and the links are the number of patent collaborations between different countries.
And my network is based only on applicants because they own the patent.
My definition of a collaborative patent is as long as the applicant's nationality is contained in more than two of the 65 countries, if there are 3, 4... For example, if the nationality is Korea, China, Russia, then part of the network is Korea-China, Korea-Russia, China-Russia and so on cross-recorded.
Since it's a non-directed network, my matrix is a 65*65 symmetric matrix.
The rows and columns of the matrix are the 65 countries and the corresponding intersections are the number of patent cooperation between different countries.
Looking forward to your reply!^_^


EPO / PATSTAT Support
Posts: 227
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Search codes for transnational cooperation patents

Post by EPO / PATSTAT Support » Fri Jun 19, 2020 1:02 pm

Hello Faery,
I assume you mean something like this (to be adapted by you):

Code: Select all

select tls206_person.person_ctry_code Applt_CTRY, 
count(distinct(case when per.person_ctry_code = 'CN' and tls206_person.person_ctry_code <> 'CN' then tls201_appln.appln_id end)) as 'CN',
count(distinct(case when per.person_ctry_code = 'DE' and tls206_person.person_ctry_code <> 'DE' then tls201_appln.appln_id end)) as 'DE',
count(distinct(case when per.person_ctry_code = 'FR' and tls206_person.person_ctry_code <> 'FR' then tls201_appln.appln_id end)) as 'FR',
count(distinct(case when per.person_ctry_code = 'JP' and tls206_person.person_ctry_code <> 'JP' then tls201_appln.appln_id end)) as 'JP',
count(distinct(case when per.person_ctry_code = 'KR' and tls206_person.person_ctry_code <> 'KR' then tls201_appln.appln_id end)) as 'KR',
count(distinct(case when per.person_ctry_code = 'TW' and tls206_person.person_ctry_code <> 'TW' then tls201_appln.appln_id end)) as 'TW',
count(distinct(case when per.person_ctry_code = 'US' and tls206_person.person_ctry_code <> 'US' then tls201_appln.appln_id end)) as 'US'
from tls201_appln
join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
join tls207_pers_appln pa on tls207_pers_appln.appln_id = pa.appln_id
join tls206_person per on pa.person_id = per.person_id
where tls201_appln.earliest_publn_year between '2014' and '2019' and appln_auth = 'EP'
and tls207_pers_appln.applt_seq_nr > 0 and tls207_pers_appln.invt_seq_nr = 0
and tls206_person.person_ctry_code in  ('CN','US','JP','KR','DE','FR','TW') 
and pa.applt_seq_nr > 0 and pa.invt_seq_nr = 0
and per.person_ctry_code in  ('CN','US','JP','KR','DE','FR','TW') 
group by tls206_person.person_ctry_code
order by tls206_person.person_ctry_code
You have to keep in mind that applicant country codes are missing for quite some patent authorities -notably China-. (See: mapping-data-completeness-of-patstat-gl ... ls231-7984 )
I therefore limited the query to patents filed at the EPO for which there is good country coverage on the applicants.
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Faery
Posts: 3
Joined: Mon May 11, 2020 6:14 am

Re: Search codes for transnational cooperation patents

Post by Faery » Wed Aug 26, 2020 8:08 am

Hello, thank you very much for your reply, I use your codes to get the excatly data that I want and I really appreciate it.
Now I have a new requirement, which is to build a directed patent citation network similar to the previous one. The purpose is to obtain an asymmetric matrix of 66*66. The nodes are 66 countries, and the edges represent the patent citation relationships between countries. For example, how many patent citations South Korea has cited from Singapore. I originally wanted to modify your previous code to obtain citation data, but it is much more complicated than I thought. I have tested new code many times but I cannot get the data I want, so I ask you for help again! Looking forward to your reply, thank you so much! ^_^


EPO / PATSTAT Support
Posts: 227
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Search codes for transnational cooperation patents

Post by EPO / PATSTAT Support » Tue Sep 01, 2020 10:50 am

Hello Faery,
in depth citation network analysis, using citation networks requires advanced and sometimes complex data aggregation models. Researchers go through quite some different considerations as to what and how to do "the counts" in function of the research question. For example: limiting to citations from the search and examiniation process (excluding applicant citations), using a "time frame", assigning the "originating first filing" as the source for the invention, etc... PATSTAT users also have to take into account the data coverage. For example: you mentioned Singapore: there are 11.702 A1 patent publications out of 63.184 for which there is citation data in PATSTAT. It seems that the data is not complete.
More information on citations coverage can be found in the "Overview of citation data in the EPO's citation database (REFI)" at this link: https://www.epo.org/searching-for-paten ... ular.html
A simplistic "count" on how many applications are cited by publications in by other patent offices can be done with the query below.

Code: Select all

select cited.publn_auth cited_appln_auth,
count(distinct(case when citing.publn_auth = 'CN'  then citing.appln_id end)) as 'CN_citing',
count(distinct(case when citing.publn_auth = 'DE'  then citing.appln_id end)) as 'DE_citing',
count(distinct(case when citing.publn_auth = 'EP'  then citing.appln_id end)) as 'EP_citing',
count(distinct(case when citing.publn_auth = 'FR'  then citing.appln_id end)) as 'FR_citing',
count(distinct(case when citing.publn_auth = 'JP'  then citing.appln_id end)) as 'JP_citing',
count(distinct(case when citing.publn_auth = 'KR'  then citing.appln_id end)) as 'KR_citing',
count(distinct(case when citing.publn_auth = 'SG'  then citing.appln_id end)) as 'SG_citing',
count(distinct(case when citing.publn_auth = 'TW'  then citing.appln_id end)) as 'TW_citing',
count(distinct(case when citing.publn_auth = 'US'  then citing.appln_id end)) as 'US_citing'
from tls211_pat_publn  citing
join tls212_citation on citing.pat_publn_id = tls212_citation.pat_publn_id
join tls211_pat_publn cited on tls212_citation.cited_pat_publn_id = cited.pat_publn_id
where year(citing.publn_date) = '2015' and citing.publn_auth in ('EP','CN','US','JP','KR','DE','FR','TW','SG') 
and cited.publn_auth in ('EP','CN','US','JP','KR','DE','FR','TW','SG') 
group by cited.publn_auth
order by cited.publn_auth
I would recommend you to do a good literature review on different patent citations analysis methods to find the one that best fit your research questions.
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply