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
How to link patstat2011 with patstat2014 through person_id?
Re: How to link patstat2011 with patstat2014 through person_
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.
-
- Posts: 176
- Joined: Tue Oct 19, 2004 10:36 am
- Location: Vienna
Re: How to link patstat2011 with patstat2014 through person_
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):
The above query will contain the matching person_id's over the 2 releases.
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
Best regards,
Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna
Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna
Re: How to link patstat2011 with patstat2014 through person_
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
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
Re: How to link patstat2011 with patstat2014 through person_
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
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