NUTS_Level is appearing as 0 in all cells

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

vima8367
Posts: 1
Joined: Tue Mar 26, 2024 2:30 pm

NUTS_Level is appearing as 0 in all cells

Post by vima8367 » Tue Mar 26, 2024 2:50 pm

Hi,

I wanted to extract the data for all PATENT applications for Deutschland from 1990 - 2023 and I tried to test the code only with any one year but in the NUTS_Level all the cells are showing 0 instead of 3.What could be the problem ?Please see the query below

Code: Select all

select
    app.appln_id, app.appln_auth, app.appln_nr, app.appln_kind,
    app.appln_filing_date,app.earliest_filing_date,
    app.docdb_family_id,app.docdb_family_size,app.nb_citing_docdb_fam,
    pers.person_name,pers.person_address,pers.person_ctry_code,pers.nuts,pers.nuts_level ,
    pers.psn_name,pers.psn_sector,pers.han_name,pers.han_harmonized,
    nace.nace2_code,
     appipc.ipc_class_symbol
 from  tls201_appln app inner join tls207_pers_appln papp 
on papp.appln_id = app.appln_id
   inner join tls206_person pers on pers.person_id = papp.person_id
 left join tls209_appln_ipc appipc on app.appln_id = appipc.appln_id
 left join tls229_appln_nace2 nace on nace.appln_id = app.appln_id
where app.appln_filing_year = 2023
  and app.appln_auth = 'de'
  and (pers.person_ctry_code = 'de' or pers.nuts like 'de%')
group by  app.appln_id, app.appln_auth, app.appln_nr, app.appln_kind,
 app.appln_filing_date,app.earliest_filing_date, app.docdb_family_id,app.docdb_family_size,app.nb_citing_docdb_fam,
 pers.person_name,pers.person_address,pers.person_ctry_code,pers.nuts,pers.nuts_level,
 pers.psn_name,pers.psn_sector,pers.han_name,pers.han_harmonized, nace.nace2_code,
  appipc.ipc_class_symbol
order by app.appln_filing_date asc;


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

Re: NUTS_Level is appearing as 0 in all cells

Post by EPO / PATSTAT Support » Fri Mar 29, 2024 9:31 am

Hello,
there are 2 issues to keep in mind.
NUTS codes can only be assigned to those applicants or inventors for which the EPO has an address available. The DE patent office does not provide the addresses to the EPO, so generally spoken no NUTS can be assigned to DE patents. Additional,the EPO data replenishing process for NUTS codes has not been carried ou properly for about 6 editions. Meaning, existing "old" applicants will probably have a NUTS code (for EP applications), and the newer ones will not. A yearly replenishment via OECD (REGPAT) data takes place, but this is not done for the complete PATSTAT data base.
Bottom line: for DE applications, it is a major job to replenish the NUTS codes manually. For EP applications, it will require in a lesser degree some data enrichment to complete it.
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


felixandrea
Posts: 1
Joined: Fri May 31, 2024 8:39 am

Re: NUTS_Level is appearing as 0 in all cells

Post by felixandrea » Fri May 31, 2024 9:35 am

Hello,
Regarding the issue you encountered when querying the PATENT data from 1990 - 2023 for Deutschland, where the NUTS_Level column is showing 0 instead of 3, there could be a few possible reasons:
1. The NUTS_Level data might be incomplete or not fully updated in the database you are using. You can check the data structure and completeness of this information in the database.
2. There could be a difference between the country code 'de' and the NUTS codes starting with 'de' in the data. You can try modifying the WHERE condition to ensure you only retrieve data with NUTS codes starting with 'de3'.
3. If you only want the data for the year 2023, you can try a similar query but with a wider time range, for example from 1990 to 2023, to see if that improves the results. rice purity test


Post Reply