EU NUTS1 Level Panel Data

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

erkul
Posts: 2
Joined: Sun Jul 30, 2017 3:44 pm

EU NUTS1 Level Panel Data

Post by erkul » Mon Jul 31, 2017 8:49 am

Hi,
I need a panel of regional patent statistics for EU (and Turkey) countries. OECD REGPAT and Eurostat data end in 2012 for regional patent statistics. For my phd dissertation, I want to add soonest possible years.

Actually, my panel has a basic structure (in MS Excel routine): rows are years (2000 to 2015) and colums are NUTS1 (or NUTS3) regions' patent application numbers.

Is there anyone who can help me with SQL inquiry for this?

Best,
Research Assistant A. Erkul


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

Re: EU NUTS1 Level Panel Data

Post by Geert Boedt » Mon Jul 31, 2017 11:32 am

Hello Erkul,
here is a sample query that will work in PATSTAT Online which gives you the number of patent publications according to the first publication year per NUTS-3 classification. (patents can have multiple publication instances for the same application, so ONLY the first one will be taken into account.)

Code: Select all

SELECT	tls904_nuts.nuts 
		,nuts_label
		,count(distinct (case when earliest_publn_year = 2010 then tls201_appln.earliest_pat_publn_id end))as "2010"
		,count(distinct (case when earliest_publn_year = 2011 then tls201_appln.earliest_pat_publn_id end))as "2011"
		,count(distinct (case when earliest_publn_year = 2012 then tls201_appln.earliest_pat_publn_id end))as "2012"
		,count(distinct (case when earliest_publn_year = 2013 then tls201_appln.earliest_pat_publn_id end))as "2013"
		,count(distinct (case when earliest_publn_year = 2014 then tls201_appln.earliest_pat_publn_id end))as "2014"
		,count(distinct (case when earliest_publn_year = 2015 then tls201_appln.earliest_pat_publn_id end))as "2015"
		,count(distinct (case when earliest_publn_year = 2016 then tls201_appln.earliest_pat_publn_id end))as "2016"
		,count(distinct (case when earliest_publn_year = 2017 then tls201_appln.earliest_pat_publn_id end))as "2017"
		,count(distinct (case when earliest_publn_year = 2018 then tls201_appln.earliest_pat_publn_id end))as "2018"
		,count(distinct (case when earliest_publn_year = 2019 then tls201_appln.earliest_pat_publn_id end))as "2019"
		,count(distinct (case when earliest_publn_year = 2020 then tls201_appln.earliest_pat_publn_id end))as "2020"
		,count(distinct (case when earliest_publn_year = 2021 then tls201_appln.earliest_pat_publn_id end))as "2021"
		,count(distinct (case when earliest_publn_year = 2022 then tls201_appln.earliest_pat_publn_id end))as "2022"
		,count (distinct (tls201_appln.earliest_pat_publn_id)) as "Total"
FROM tls904_nuts
	  join tls206_person on tls904_nuts.nuts = tls206_person.nuts
	  join tls207_pers_appln on tls206_person.person_id = tls207_pers_appln.person_id
	  join tls201_appln on tls207_pers_appln.appln_id = tls201_appln.appln_id
where tls904_nuts.nuts  like 'tr%' and appln_auth = 'EP' and appln_kind = 'A' and applt_seq_nr > 0 and tls201_appln.earliest_publn_year > 2009
and tls904_nuts.nuts_level = 3
group by tls904_nuts.nuts 
		,nuts_label
order by total desc
A couple of observations:
I used first publication year, but you could equally use the earliest_filing_year. That depends on the nature of your research. I limited the count to EP applications - this includes PCT applications that have entered the regional phase. Because that procedure takes a longer time for the patents to be published with an EP number, the total amount for 2015 might still be incomplete because the source files for the Spring editions are extracted in January. If you use the same query for non-EP applications, then the figures will be incomplete because of missing address data which is needed to assign NUTS codes. (you can of course deal with this from a statistical point of view if you would need figures closer to reality.)
I also limited to only taking the Turkish applicants into account. So a Turkish inventor (that is not an applicant at the same time); with a Turkish address, working for a German company with German address will not contribute to the count.
Multiple applicants for 1 application will only result in 1 count (because of the DISTINCT clause)
Best regards,

Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna


erkul
Posts: 2
Joined: Sun Jul 30, 2017 3:44 pm

Re: EU NUTS1 Level Panel Data

Post by erkul » Wed Aug 02, 2017 8:03 am

Dear Boedt,
Many thanks for your quick reply. It helped much for my purpose.

Since I study regional innovation systems, the region from where patents are filed is crucial. So, your indication of applicant nationality is fitting. Also, in my research, I limited applications to EPO for easiness and comparison.

Again, thanks for your help :)


Post Reply