Patent fractional count and forward citations

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

Simone Robbiano
Posts: 2
Joined: Thu Jun 25, 2020 11:36 am

Patent fractional count and forward citations

Post by Simone Robbiano » Thu Jun 25, 2020 11:47 am

Good morning, everybody,
at the moment I am conducting an empirical work for which I need all filed patents at nuts-3 level, for Italy, for years from 1978 to 2015 (with them I subsequently create a fractional patent count with Stata).
So far I have successfully used the following query, running it individually for each year:

Code: Select all

SELECT APP.appln_id, APP.appln_auth, APP.appln_nr, APP.appln_kind, APP.appln_filing_date, APP.appln_filing_year, APP.appln_nr_epodoc, APP.appln_nr_original, APP.ipr_type, APP.receiving_office, APP.internat_appln_id, APP.int_phase, APP.reg_phase, APP.nat_phase, APP.earliest_filing_date, APP.earliest_filing_year, APP.earliest_filing_id, APP.earliest_publn_date, APP.earliest_publn_year, APP.earliest_pat_publn_id, APP.granted, APP.docdb_family_id, APP.inpadoc_family_id, APP.docdb_family_size, APP.nb_citing_docdb_fam, APP.nb_applicants, APP.nb_inventors, APT.appln_title, P.person_id, P.person_name, P.person_address, P.person_ctry_code, P.doc_std_name_id, P.doc_std_name, P.psn_id, P.psn_name, P.psn_level, P.psn_sector, P.han_id, P.han_name, P.han_harmonized, PA.applt_seq_nr, PA.invt_seq_nr, PO.city, PO.zip_code, PO.person_ctry_code, PO.residence_ctry_code, PO.role, APIPC.ipc_class_symbol, APIPC.ipc_class_level, APIPC.ipc_version, APIPC.ipc_value, APIPC.ipc_position, APIPC.ipc_gener_auth, NA2.nace2_code, NA2.weight, ATF.techn_field_nr, ATF.weight, N.nuts, N.nuts_level, N.nuts_label, PPB.pat_publn_id, PPB.publn_auth, PPB.publn_nr, PPB.publn_kind, PPB.publn_date, PPB.publn_first_grant, PPB.publn_claims, CIT.citn_replenished, CIT.citn_id, CIT.citn_origin, CIT.cited_pat_publn_id, CIT.cited_appln_id, CIT.pat_citn_seq_nr, CIT.cited_npl_publn_id, CIT.npl_citn_seq_nr, CIT.citn_gener_auth
	FROM tls904_nuts N
 		 join tls206_person P on N.nuts = P.nuts
 		 join tls207_pers_appln PA on P.person_id = PA.person_id
 		 join tls201_appln APP on PA.appln_id = APP.appln_id
         join tls209_appln_ipc APIPC on APP.appln_id = APIPC.appln_id
         join tls226_person_orig PO on PA.person_id = PO.person_id
         join tls202_appln_title APT on APP.appln_id = APT.appln_id
         join tls211_pat_publn PPB on APP.appln_id = PPB.appln_id
         join tls229_appln_nace2 NA2 on APP.appln_id = NA2.appln_id
         join tls230_appln_techn_field ATF on APP.appln_id = ATF.appln_id
         join tls212_citation CIT on PPB.pat_publn_id = CIT.pat_publn_id
	WHERE N.nuts like 'IT%'
    	AND N.nuts_level=3
		AND APP.appln_filing_date >= '2015-01-01' 
		AND APP.appln_filing_date <= '2015-12-31'
GROUP BY N.nuts, N.nuts_level, N.nuts_label, APP.appln_id, APP.appln_auth, APP.appln_nr, APP.appln_kind, APP.appln_filing_date, APP.appln_filing_year, APP.appln_nr_epodoc, APP.appln_nr_original, APP.ipr_type, APP.receiving_office, APP.internat_appln_id, APP.int_phase, APP.reg_phase, APP.nat_phase, APP.earliest_filing_date, APP.earliest_filing_year, APP.earliest_filing_id, APP.earliest_publn_date, APP.earliest_publn_year, APP.earliest_pat_publn_id, APP.granted, APP.docdb_family_id, APP.inpadoc_family_id, APP.docdb_family_size, APP.nb_citing_docdb_fam, APP.nb_applicants, APP.nb_inventors, APT.appln_title, P.person_id, P.person_name, P.person_address, P.person_ctry_code, P.doc_std_name_id, P.doc_std_name, P.psn_id, P.psn_name, P.psn_level, P.psn_sector, P.han_id, P.han_name, P.han_harmonized, PA.applt_seq_nr, PA.invt_seq_nr, PO.city, PO.zip_code, PO.person_ctry_code, PO.residence_ctry_code, PO.role, APIPC.ipc_class_symbol, APIPC.ipc_class_level, APIPC.ipc_version, APIPC.ipc_value, APIPC.ipc_position, APIPC.ipc_gener_auth, NA2.nace2_code, NA2.weight, ATF.techn_field_nr, ATF.weight, PPB.pat_publn_id, PPB.publn_auth, PPB.publn_nr, PPB.publn_kind, PPB.publn_date, PPB.publn_first_grant, PPB.publn_claims, CIT.citn_replenished, CIT.citn_id, CIT.citn_origin, CIT.cited_pat_publn_id, CIT.cited_appln_id, CIT.pat_citn_seq_nr, CIT.cited_npl_publn_id, CIT.npl_citn_seq_nr, CIT.citn_gener_auth
Recently, I felt the need to include a measure on the number of citations received by each individual patent (forward citations) but I cannot correct the above query: any advice?

Thank you very much to everyone


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

Re: Patent fractional count and forward citations

Post by EPO / PATSTAT Support » Fri Jun 26, 2020 2:46 pm

Dear Simone,
If you can live with the "family based" forward citations, then simply take the nb_citing_docdb_fam attribute.
"Application based" data would involve using the tls211 and tls212 tables and then "grouping" the data at application level. This is quasi impossible to do via PATSTAT Online as it requires intermediate tables to be made. The only solution I would see is to extract a data set (your query generates less then 5000 patent applications), and then do the analysis in MS ACCESS or any other data management tool.
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Simone Robbiano
Posts: 2
Joined: Thu Jun 25, 2020 11:36 am

Re: Patent fractional count and forward citations

Post by Simone Robbiano » Fri Jun 26, 2020 2:57 pm

Thank you very much.
I suspected there was no easy way to do this through Patstat Online: I'll try to implement your hints.
Best regards

Simone Robbiano


Post Reply