Make a Patstat query based on publication numbers

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

cako
Posts: 8
Joined: Thu Feb 11, 2016 7:08 pm

Make a Patstat query based on publication numbers

Post by cako » Tue May 24, 2016 7:50 am

Hi,
Imagine that I have a list of many publication numbers:

US2016138562, WO2014198277, WO2014198277, …

Now I want to investigate these in Patstat - is there an easy way of entering these into a Patstat query?

The problem is that the publication number in the tls211_pat_publn is divided into publn_auth and publn. My ‘solution’ so far has been to go to espacenet and retrieve application numbers:
US201615007680, WO2014DK50164, WO2014DK50164, …

And then find the applications via:
tls201_appln.appln_nr_epodoc in ('US201615007680' , 'WO2014DK50164' , 'WO2014DK50164')

Is there a better/smarter way of doing this?

I hope that you can help
Thanks,
Carsten


mkracker
Posts: 97
Joined: Wed Sep 04, 2013 6:17 am
Location: Vienna

Re: Make a Patstat query based on publication numbers

Post by mkracker » Tue May 24, 2016 1:56 pm

A simple way to do this is to concatenate the PUBLN_AUTH and the PUBLN_NR before doing the comparison. In T-SQL (MS SQL Server) this would look like this:

Code: Select all

select* from tls211_pat_publn
where publn_auth + publn_nr = 'WO2014198277'
Please note that you will not find publication US2016138562 in the current PATSTAT edition, because this publication was published just last week (May 2016), but the current PATSTAT 2016 Spring edition has only publications up to January 2016.

There are several different number formats for publications and applications. Starting with the 2016 Spring edition, PATSTAT will have - in addition - the "original" application and publication numbers, as delivered by the national offices. No harmonisation or re-formatting has been applied to these numbers. This can be useful if you have to combine PATSTAT with the original source of a patent office. Note, however, that for publications we only have the original number format in about 20% of the cases, so it's best to check the coverage before doing any serious analysis.
-------------------------------------------
Martin Kracker / EPO PATSTAT


cako
Posts: 8
Joined: Thu Feb 11, 2016 7:08 pm

Re: Make a Patstat query based on publication numbers

Post by cako » Thu May 26, 2016 2:26 pm

Thank you for the simple, yet elegant and effective solution to my problem!

Best regards,
Carsten


Post Reply