Spanish universities publications in 2017

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

jantonio.peces
Posts: 8
Joined: Mon Apr 18, 2016 1:22 pm

Spanish universities publications in 2017

Post by jantonio.peces » Tue Jul 25, 2017 8:38 am

Hi Martin and Geert,
I need some help to find out if I wrote correctly my query.
I am searching for patent publications applied by Spanish Universities that were publised in 2017.
Please see below if I did it correctly.
Thanks a lot. Greetings from Spain.
José A. Peces OEPM

Code: Select all

SELECT DISTINCT a. appln_nr, a. appln_filing_date, a. docdb_family_size, publn_auth, publn_nr, person_ctry_code , person_name
FROM tls201_appln a
JOIN tls211_pat_publn pn ON a.appln_id = pn.appln_id
JOIN tls207_pers_appln pa on a.appln_id = pa.appln_id
JOIN tls206_person p on pa.person_id = p.person_id
JOIN tls209_appln_ipc b on b.appln_id = pa.appln_id
WHERE publn_date > '20170101'
AND person_name like 'universi%'
AND person_ctry_code = 'ES'
AND appln_kind = 'A' 
AND a.appln_id < 900000000


Geert Boedt
Posts: 176
Joined: Tue Oct 19, 2004 10:36 am
Location: Vienna

Re: Spanish universities publications in 2017

Post by Geert Boedt » Tue Jul 25, 2017 2:35 pm

Hello José,
your query is correct; but a couple of small remarks.
Generally spoken, if you really want to limit to applicants, you should force the applt_seq_nr to be > 0 . In your query it does not make any difference because of the conditions on the person_name. Also, as you are looking for publications applied by Spanish Universities, one could make use of PSN_SECTOR attribute to retrieve possible "university" applicants that do not fulfil the like 'universi%' condition. The code below could be added in your query.

Code: Select all

AND (person_name like 'universi%' or psn_sector in ('UNIVERSITY','UNIVERSITY HOSPITAL'))
AND applt_seq_nr > 0
Your query will result in 101 applications. It is important to remember that PATSTAT is produced only 2 times/year, and will therefore never show the "very latest" of the available data. If you then limit to publications in 2017, this restriction really limits the result and is not representative. The SPRING 2017 PATSTAT version is produced on the backfile extraction January 2017, any you can see from the result that all those publications are dated in January. So this is not so good.

Specific for your case; to get more up-to-date results I would use the GPI (Global Patent Index), which is weekly updated.

Approach: I extracted in PATSTAT all the "DOC_STD_NAME" occurrences for Spanish universities. Based on the PATSTAT list (which included a couple of false positives which I cleaned out), I then used excel to create the GPI query (which is not SQL based), followed by a search and extraction in GPI. This results in 962 documents. Data on those documents were then extracted as an excel sheet. (In the GPI preferences, there are several options on what data fields can be extracted.) Further data aggregation in GPI is more complicated, but if the purpose is simply a list, then I think this will suit your needs. You can equaly download the PDF's.
Attached is the excel sheet with the names from PATSTAT, GPI query and results.
ES_UNIV.xlsx
(141.92 KiB) Downloaded 225 times
Best regards,

Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna


jantonio.peces
Posts: 8
Joined: Mon Apr 18, 2016 1:22 pm

Re: Spanish universities publications in 2017

Post by jantonio.peces » Wed Jul 26, 2017 9:02 am

thanks a lot!!!


Maria_Mercedes
Posts: 3
Joined: Tue Aug 08, 2017 5:22 pm

Re: Spanish universities publications in 2017

Post by Maria_Mercedes » Wed Aug 09, 2017 9:05 pm

Hello Jose A and Geert,
I am doing a bibliometric study in PASTAT and the file 'patent publications of Spanish universities in 2017' it could be very useful for me to analyse citing NPLs of the patents.
I have tried to join identification data of patents as inventors, applicants with bibliographic data of references using Access but I didnt get it.
I would be possible to obtain this information.
Thanks in advance,


Post Reply