Backward and forward citations; Problems with syntax

[Maria_Mercedes]

Good afternoon,
I am trying to extract information from PATSTAT (2017) about Biotechnology. More especifically, I would like to analyse backward bibliographic citations included in patents.
Because I dont know SQL language, could be possible someone publish a script with the sintax:
-Patents that include NPL
-Topic: Biotechnology
-Dates: 2007-2017
Finally, technically how could I covert XLM in CSV or Excel files?
Many enquiries...
Thanks

[Geert Boedt]

Hello Maria_Mercedes,
indeed a lot of questions. It will be rather difficult to work with PATSTAT (Online) without any sql knowledge. On the PATSTAT website, you can find some self training material and sample queries to get started. Here is the link: http://www.epo.org/searching-for-patent ... html#tab-1

Specific to your question:
-Patents that include NPL: I assume you mean patents that have NPL citations.
-Topic: Biotechnology: most researchers would use IPC or CPC classification codes to narrow down to the technical area they want to have included. Alternative is to use the tls901_techn_field_ipc table to identify "Biotechnology" applications --> this will maybe extend to scope too much.
-Dates: 2007-2017: let's assume you are referring to publication dates - 2017 applications will only be published in 2018, but you need to have a closer look at the meaning of application, publication or priority date.
Here is a sample query that will give you for all EP - A1 publications, published in 2016, that had a "Biotechnology" classification, the list of the cited NPL documents. This list includes all different kinds of NPL citations (defined through the citn_origin attribute).
This list will give you about 54.000 records; extending the date range will give a manifold of results. Depending of the kind of research you are doing, you will need to aggregate further.

Code: Select all

select distinct tls211_pat_publn.publn_auth,tls211_pat_publn.publn_nr ,tls211_pat_publn.publn_kind, tls211_pat_publn.publn_date, techn_field,citn_origin,npl_citn_seq_nr, npl_biblio 
from tls211_pat_publn join tls230_appln_techn_field on tls211_pat_publn.appln_id = tls230_appln_techn_field.appln_id
join tls901_techn_field_ipc on  tls230_appln_techn_field.techn_field_nr = tls901_techn_field_ipc.techn_field_nr
join tls212_citation on tls211_pat_publn.pat_publn_id = tls212_citation.pat_publn_id
join tls214_npl_publn on tls212_citation.cited_npl_publn_id = tls214_npl_publn.npl_publn_id
where techn_field = 'Biotechnology' and publn_auth = 'EP' and publn_kind = 'A1' and year(publn_date) = '2016' and npl_citn_seq_nr <> 0
order by tls211_pat_publn.publn_nr,citn_origin,npl_citn_seq_nr  

[Maria_Mercedes]

Hello Geert,
Thank you very much for the query, your search strategy has saved me many hours of work!
I tryed to modify the initial search. For that, I have selected patents from Spanish researchers, which sector is the university and the earliest filing date is '2014'. I have exported the results to Access, and there I have created a query with the technological field as well as the bibliographic data of patents and references inserted in patents.
Thanks again and best regards,
Mercedes

select distinct tls211_pat_publn.publn_auth,tls211_pat_publn.publn_nr ,tls211_pat_publn.publn_kind, tls211_pat_publn.publn_date,citn_origin,npl_citn_seq_nr, npl_biblio, tls201_appln.appln_id from tls211_pat_publn join tls201_appln on tls211_pat_publn.appln_id = tls201_appln.appln_id 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 tls209_appln_ipc on tls207_pers_appln.person_id = tls209_appln_ipc.appln_id join tls212_citation on tls211_pat_publn.pat_publn_id = tls212_citation.pat_publn_id join tls214_npl_publn on tls212_citation.cited_npl_publn_id = tls214_npl_publn.npl_publn_id where earliest_filing_date between '20140101' and '20150101' and publn_auth = 'ES' and tls201_appln.appln_id < 900000000 AND psn_sector = 'University' and publn_kind = 'A1' and npl_citn_seq_nr <> 0 order by tls211_pat_publn.publn_nr,citn_origin,npl_citn_seq_n