Measuring of radical innovation (SQL)

[torben]

Hello PATSTAT community,

for my thesis i want to measure the radicalness of innovation with help of patent data. I found a method to measure published by the OECD. I'm using an online access of PATSTAT. But my problem is, that i can't translate the algorithm into the SQL format of PATSTAT.
I want to measure the radicalness of about 300 previous defined patents. I've got the appln_id of the 300 patents.

That's the algorithm published by the OECD
"
Radicalness index (t-SQL script)

UPDATE [Indicator_table]
SET Radicalness = calc.Radicalness
FROM [Indicator_table] i
INNER JOIN
(SELECT t1.Citing_Appln_id, SUM(t1.IPC_Count)/t2.IPC_Total as 'Radicalness'
FROM (SELECT cit.Citing_Appln_id, cited.Appln_id, cited.IPC, cited.IPC_Count, citing.Citing_IPC'
FROM ((SELECT Citing_Appln_id, Cited_Appln_id
FROM [OECD_CIT_EP_Citations])
UNION
(SELECT c.Citing_Appln_id, w.Cited_Appln_id
FROM [OECD_CIT_EP_Citations] c
INNER JOIN [OECD_CIT_EP_CIT_Counts] e
ON e.EP_Appln_id=c.Citing_appln_id
INNER JOIN [OECD_CIT_WO_Citations] w
ON w.Citing_appln_id = e.WO_Appln_id) ) cit
INNER JOIN
(SELECT Appln_id, LEFT(Class_Symbol, 4) as 'IPC',
COUNT(distinct Class_Symbol) as 'IPC_Count'
FROM [PATSTAT_APPLN_IPC]
GROUP BY Appln_id, LEFT(Class_symbol, 4)) cited
ON cited.Appln_id = cit.Cited_appln_id
LEFT OUTER JOIN
(SELECT DISTINCT i.Appln_id, LEFT(i.Class_Symbol, 4) as 'Citing_IPC'
FROM [PATSTAT_APPLN_IPC i) citing
ON cit.Citing_appln_id = citing.Appln_id AND cited.IPC = citing.IPC) t1
INNER JOIN
(SELECT Cit.Citing_Appln_id, sum(Cited.IPC_Total) as 'IPC_Total'
FROM ((SELECT Citing_Appln_id, Cited_Appln_id
FROM [OECD_CIT_EP_Citations])
UNION
(SELECT c.Citing_Appln_id, w.Cited_Appln_id
FROM [OECD_CIT_EP_Citations] c
INNER JOIN [OECD_CIT_EP_CIT_Counts] e
ON e.EP_Appln_id=c.Citing_appln_id
INNER JOIN [OECD_CIT_WO_Citations] w
ON w.Citing_appln_id = e.WO_Appln_id) ) cit
INNER JOIN
(SELECT Appln_id, COUNT(distinct Class_Symbol) as 'IPC_Total'
FROM [PATSTAT_APPLN_IPC]
GROUP BY Appln_id) cited
ON cited.Appln_id = cit.Cited_appln_id
GROUP BY Cit.Citing_Appln_id) t2
ON t2.Citing_appln_id = t1.Citing_appln_id
WHERE t1.Citing_IPC is NULL
GROUP BY t1.Citing_appln_id, t2.IPC_Total) calc
ON i.Appln_id = calc.Citing_appln_id
WHERE i.Bwd_Cits > 0


Note: Appln_id refer to PATSTAT surrogate keys of each patent document registered in PATSTAT.
Source: OECD, October 2012. T-SQL Script developed under Microsoft® SQL Server – based on variables provided in PATSTAT
(EPO, April 2012) and in the Citation database, OECD, June 2012.
© OECD. This algorithm has been developed by the OECD and is made freely available for public use, subject to acknowledgement
of the source using the official citation on the cover of this document.

"

The source: http://www.oecd-ilibrary.org/docserver/ ... 720B435874
Page 56

[Geert Boedt]

Dear Torben,

the OECD SQL query you cited in your post can not run on PATSTAT Online because it needs pre-aggregated tables that have been created by OECD. The query also creates (and updates) tables, and that is not possible on PATSTAT Online. (PATSTAT Online is strictly read only for obvious reasons.) According to the referred document, the OECD tables can be obtained upon a request sent to sti.microdatalab @ oecd.org.
So the easy solution would be to request the ready made table and simply look-up the 300 patents you have in your sample. I assume you will need to use a database platform (MS ACCESS should be sufficient) to load the table. If OECD calculated the values for all EP applications, then there will probably be too many records to simply use Excel.
The not-so-easy solution is to purchase the PATSTAT data base and then re-produce the various OECD tables. A closer look at the script shows that the scrip also need the OECD citation database. They are equally available from OECD on request.

[torben]

Hello Geert,
thank you for your reply.
I will try your easy solution. Thanks

Can you also look at another question of mine at this topic : patstat/topic4691.html