How to link patstat2011 with patstat2014 through person_id?

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

LN88
Posts: 3
Joined: Tue Apr 05, 2016 10:19 am

How to link patstat2011 with patstat2014 through person_id?

Post by LN88 » Fri Apr 08, 2016 8:59 am

Dear all,

I am new to patstat and was wondering how one could relate two different time versions of patstat through person_id. Apparently person_id changes between versions and I would like a two-columns concordance table in order to make meaningful comparisons.

Thank you,
Luca


LN88
Posts: 3
Joined: Tue Apr 05, 2016 10:19 am

Re: How to link patstat2011 with patstat2014 through person_

Post by LN88 » Fri Apr 08, 2016 10:04 am

In other words, if the same company applied for some patents both in 2011 and 2014, I would like it to have the same applicant_id.


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

Re: How to link patstat2011 with patstat2014 through person_

Post by Geert Boedt » Mon Apr 11, 2016 3:20 pm

Hello Luca,
In principle, there should not be any patent applications in PATSTAT2011 which you can not retrieve in PATSTAT 2014. (Except for dummies having appln_id > 900000000.) So at application level, there should not be any problem. Your question for the matching person_id's between the versions is a bit more tricky. Person_id's are in principle stable starting from PATSTAT 2013 Autumn edition. There are some exceptions where some data has been corrected/changed and the person_id was not changed. I only see one case where a concordance table at person_id level might be useful, for example when you have carried out a lot of manual work in selecting/matching/grouping/linking a large amount of persons, and repeating this process for the newer releases with the new databases would require a lot of work to be redone. Solution: a person_id is a unique -now stable- surrogate key based on the following 3 attributes: person_name, person_address and person_country code. So if 2 records have those 3 attributes being equal, then it will be considered the same person, and only be represented once. This principle is still used for the new releases. So you can use this to create a concordance table between PATSTAT 2011 and PATSTAT 2014.
The SQL looks as follows (you need to have the tls206_person data from the 2 releases to do this, I took 2012 because we don't have a PATSTAT 2011 data base anymore):

Code: Select all

SELECT TOP 1000
 tls206_2012.person_id
,tls206_2012.person_name
,tls206_2012.person_address
,tls206_2012.person_ctry_code
,tls206_2014.person_id
,tls206_2014.person_name
,tls206_2014.person_address
,tls206_2014.person_ctry_code
FROM [patstat2012a].[dbo].[tls206_person] as tls206_2012,
[patstat2014b].[dbo].[tls206_person] as tls206_2014
where
tls206_2012.person_name = tls206_2014.person_name
and tls206_2012.person_address = tls206_2014.person_address
and tls206_2012.person_ctry_code = tls206_2014.person_ctry_code
The above query will contain the matching person_id's over the 2 releases.
Best regards,

Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna


LN88
Posts: 3
Joined: Tue Apr 05, 2016 10:19 am

Re: How to link patstat2011 with patstat2014 through person_

Post by LN88 » Tue Apr 12, 2016 9:39 am

Dear Geert,

many thanks for this very useful reply. I will work on it and come back to you here if additional doubts arise.

Best regards,
Luca


alegasi
Posts: 1
Joined: Fri Jul 29, 2016 10:31 am

Re: How to link patstat2011 with patstat2014 through person_

Post by alegasi » Fri Jul 29, 2016 10:42 am

Hello Luca!

Were you successful in creating a concordance table?
I would need to connect person_id's from 04.2011 and the current version (>2013) to use results of Patstat<->Amadeus disambiguation algorithm. Unfortunately, I have access only to the latest database with stable patstat_id's. Would you be able to share a concordance table linking two PATSTAT versions?

Thanks, Oleg


Post Reply