iangow/mult_permnos.md

## mult_permnos.md

      
    Raw
  

              mult_permnos.md
            
          
    library(dbplyr)
library(dplyr, warn.conflicts = FALSE)
library(DBI)
pg <- dbConnect(RPostgres::Postgres(), 
                bigint = "integer", 
                check_interrupts = TRUE)
ccmxpf_lnkhist <- tbl(pg, sql("SELECT * FROM crsp.ccmxpf_lnkhist")) 
stocknames <- tbl(pg, sql("SELECT * FROM crsp.stocknames"))

ccm_link <-
  ccmxpf_lnkhist %>%
  filter(linktype %in% c("LC", "LU", "LS"),
         linkprim %in% c("C", "P")) 

ccm_link %>%
  select(gvkey, lpermno) %>%
  distinct() %>%
  group_by(gvkey) %>%
  mutate(num_permnos = n()) %>%
  filter(num_permnos==5L) %>%
  inner_join(ccmxpf_lnkhist, by = c("gvkey", "lpermno")) %>%
  rename(permno=lpermno) %>%
  inner_join(stocknames, by = "permno") %>%
  arrange(linkdt) %>%
  select(gvkey, permno, linkdt, linkenddt, ncusip, comnam)
#> # Source:     lazy query [?? x 6]
#> # Database:   postgres [igow@/var/run/postgresql:5434/crsp]
#> # Groups:     gvkey
#> # Ordered by: linkdt
#>   gvkey  permno linkdt     linkenddt  ncusip   comnam                     
#>   <chr>   <dbl> <date>     <date>     <chr>    <chr>                      
#> 1 032280  81875 1995-08-11 1999-03-09 87924V50 TELE COMMUNICATIONS INC NEW
#> 2 032280  86746 1999-03-10 2001-08-09 00195720 A T & T CORP               
#> 3 032280  89130 2001-08-10 2006-05-09 53071810 LIBERTY MEDIA CORP NEW     
#> 4 032280  89130 2001-08-10 2006-05-09 53071810 LIBERTY MEDIA CORP NEW     
#> 5 032280  91275 2006-05-10 2011-09-25 53071M30 LIBERTY MEDIA CORP NEW     
#> 6 032280  13008 2011-09-26 2016-12-30 85571Q10 STARZ                      
#> 7 032280  13008 2011-09-26 2016-12-30 53032210 LIBERTY MEDIA CORP 2ND NEW 
#> 8 032280  13008 2011-09-26 2016-12-30 53032210 LIBERTY MEDIA CORP 2ND NEW
^{Created on 2021-02-14 by the reprex package (v0.3.0)}