7,627
edits
Changes
Jump to navigation
Jump to search
And There are two obvious master tables that we could export and reuse. The first is patenthistwoprebuys . This is patenthistorybase (1513045) but ''WHERE startdate < enddate''. It has 1,495,486 records and the unit of observation is a solid master table:patent associated with a firm. The keys are gvkey and patentnumber.
Therefore needed inputs to DataExtension.sql are:*patentexpiry*stdcompnames*assignees*StataStaging4 ===PatentExpiry=== The '''patentexpiry''' second master table was built in '''allpatentsprocessed''' by running PatentExpiry.sql, which is in E:\mcnair\Projects\LBO\Clean\Ed Run and was last updated in March 2018gvkeyyearpatentdata. Most of the script is comment. The key elements are: The stack is as follows:*'''patents''' This is an input*Maintfeeevents2 FROM 'MaintFeeEvents_20170410b.txt' (COUNT 14708262)*PatentExpirationRules2 FROM 'patentExpirationRules2.txt' (COUNT 27)*Maintfeeevents2aggregated table,patentexpirationrules where the unit of observation is gvkey-> patentfeebaseevents (4402215)*FULL OUTER (patentsyear, patentfeebaseevents) -> patentfeebasefull (5463947) -> PatentExpiryBase (5463947) where year goes from 1975-> PatentExpiry 2017 (5463947see GvkeyYear above) Note that patentfeebaseevents is made from PatentExpirationRules and not PatentExpirationRules2contains zeros. Both list the same fee codes and descriptionThere are 395, but PatentExpirationRules2 has a termlength column whereas PatentExpirationRules has daysincrement and daysduration columns. daysduration is the same as termlength, except when daysduration=7300 then termlength=6205213 observations (i. It seems from the code that PatentExpirationRules is correct, as there is a manual fix when max(daysduration)=7300e. DROP TABLE PatentExpiry; CREATE TABLE PatentExpiry AS SELECT patentnumber, FilingDate, grantdate, maxdaysduration, nomaintenance, maxtermfix1, maxtermfix2, CASE --Old patents get: grant plus 17yrs WHEN nomaintenance = 1 THEN grantdate + 6205 -- Patents grandfathered in during transition in 1995 to new file date + 20 one for each year term limit get: Later of issue date + 17 years or file date + 20 years used WHEN (maxtermfix1 = 1 AND maxdaysduration = 7300) THEN GREATEST(FilingDate + 7300for 9, grantdate + 6205191 unique GVKEYs) --Patents after June 8 1995 but before May 28 2000 get: file date + 20 years WHEN (maxtermfix2 = 1 AND maxdaysduration = 7300) THEN FilingDate + 7300 --Patents after May 28 2000 (this might not be automatic and require an appeal though) get: later of issue date + 17 years or file date + 20 years WHEN (maxtermfix2 = 2 AND maxdaysduration = 7300) THEN GREATEST(FilingDate + 7300, grantdate + 6205) ELSE grantdate + maxdaysduration END AS expdate, CASE WHEN nomaintenance = 1 OR maxdaysduration = 7300 THEN 1::int ELSE 0::int END AS HeldToTerm FROM PatentExpiryBase; --5463947.
Note: It also looks like Ed did the same fixes in:E:\mcnair\Projects\LBO\Clean\Ed Run\Building the Patent Start End Table Revised"public.sqlgvkeyyearpatentdata" Column | Type | Collation | Nullable | Default --------------------+---------+-----------+----------+--------- gvkey | text | | | year | integer | | | nogrants | bigint | | | nobuy | bigint | | | nosell | bigint | | | noterm | bigint | | | nonotnenewed | bigint | | | nounknownexp | bigint | | | portfoliochg | bigint | | | portfolio | numeric | | | portfoliocitations | bigint | | | citesperpatent | numeric | | |
The Effect of Leverage Buyouts on Innovation (view source)
Revision as of 11:53, 26 June 2019
, 11:53, 26 June 2019→In 2019
ORDER BY gvkeyint, year;
--8648
Required input tables are:
*HistMasterFile, which is the NBER historical masterfile FROM 'historical_masterfile.csv'
*citations
*patents
All three are in '''allpatentsprocessed'''
===PatentExpiry===
The '''patentexpiry''' table was built in '''allpatentsprocessed''' by running PatentExpiry.sql, which is in E:\mcnair\Projects\LBO\Clean\Ed Run and was last updated in March 2018. Most of the script is comment. The key elements are:
The stack is as follows:
*'''patents''' is an input
*Maintfeeevents2 FROM 'MaintFeeEvents_20170410b.txt' (COUNT 14708262)
*PatentExpirationRules2 FROM 'patentExpirationRules2.txt' (COUNT 27)
*Maintfeeevents2,patentexpirationrules -> patentfeebaseevents (4402215)
*FULL OUTER (patents, patentfeebaseevents) -> patentfeebasefull (5463947) -> PatentExpiryBase (5463947) -> PatentExpiry (5463947)
Note that patentfeebaseevents is made from PatentExpirationRules and not PatentExpirationRules2. Both list the same fee codes and description, but PatentExpirationRules2 has a termlength column whereas PatentExpirationRules has daysincrement and daysduration columns. daysduration is the same as termlength, except when daysduration=7300 then termlength=6205. It seems from the code that PatentExpirationRules is correct, as there is a manual fix when max(daysduration)=7300.
DROP TABLE PatentExpiry;
CREATE TABLE PatentExpiry AS
SELECT patentnumber, FilingDate, grantdate, maxdaysduration, nomaintenance, maxtermfix1, maxtermfix2,
CASE
--Old patents get: grant plus 17yrs
WHEN nomaintenance = 1 THEN grantdate + 6205
-- Patents grandfathered in during transition in 1995 to new file date + 20 year term limit get: Later of issue date + 17 years or file date + 20 years used
WHEN (maxtermfix1 = 1 AND maxdaysduration = 7300)
THEN GREATEST(FilingDate + 7300, grantdate + 6205)
--Patents after June 8 1995 but before May 28 2000 get: file date + 20 years
WHEN (maxtermfix2 = 1 AND maxdaysduration = 7300)
THEN FilingDate + 7300
--Patents after May 28 2000 (this might not be automatic and require an appeal though) get: later of issue date + 17 years or file date + 20 years
WHEN (maxtermfix2 = 2 AND maxdaysduration = 7300)
THEN GREATEST(FilingDate + 7300, grantdate + 6205)
ELSE grantdate + maxdaysduration
END AS expdate,
CASE WHEN nomaintenance = 1 OR maxdaysduration = 7300 THEN 1::int ELSE 0::int END AS HeldToTerm
FROM PatentExpiryBase;
--5463947
Note: It also looks like Ed did the same fixes in:
E:\mcnair\Projects\LBO\Clean\Ed Run\Building the Patent Start End Table Revised.sql
===Analyzing DataExtension.sql===
*patenthistorybase ...,GvkeyYear -> gvkeyyearpatentdata (395213)
Therefore needed inputs to DataExtension.sql are:
*patentexpiry
*stdcompnames
*assignees
*StataStaging4
Note that Patentsassigneessimple is not in '''allpatentsprocessed''' but assignee is, and it can easy be rebuilt:
--5426556
Table "public.patenthistwoprebuys"
Column | Type | Collation | Nullable | Default
unknownexp | integer | | |
===StataStaging4===
And the best instructions seem to be to run parts 1, 2, and 3A of that script.
==The Plan==
The plan is to build a new database made from the following components:
*LBO data with GVKEYs, preferably cleaned to deal with durations (etc.), imported directly from a spreadsheet
*New draw of the universe COMPUSTAT data, with all appropriate variables, covering 1975-2017 (or 2018 if possible), processed to remove duplicates etc.
*Imports of '''patenthistwoprebuys''' and '''gvkeyyearpatentdata''', which are available in '''allpatentsprocessed'''
*Imports of '''patents''', '''citations''', and '''HistMasterFile''', all of which are available in '''allpatentsprocessed''' -- we can then run the code from PatentMeasures.sql to generate the merge data once we've done the matching.
==Ed's version from 2018==
