Downloading only transportation companies from the WRDS

Question

I am trying to download data from wrds running an SQL query on my R studio. However I keep getting the same mistake.

I am trying to download data only for transportation companies (NSDINX code 6) but I get error messages. It did not happen when selecting the nasdaq index (HEXCD = 3)

res <- dbSendQuery(wrds, "select CUSIP, DATE, PERMNO, PERMCO, PRC, RET, RETX, VOL, HEXCD, NSDINX from CRSP.DSF WHERE DATE >= '2000-03-01' AND DATE < '2018-04-01' AND NSDINX = 6 AND HEXCD = 3")

Error in result_create(conn@ptr, statement) : 
  Failed to prepare query: ERROR:  column "nsdinx" does not exist
LINE 1: ...IP, DATE, PERMNO, PERMCO, PRC, RET, RETX, VOL, HEXCD, NSDINX

What is this supposed to be? `DATE < '2018-04-01' NSDINX = 6`? — Gordon Linoff, Jun 17 '19 at 21:12
The error is clear, the column `NSDINX` doesn't exist in the `DSF` table. What are you trying to find clearer than the error message itself? — James, Jun 17 '19 at 21:32
@JaimeDrq I am trying to download the daily returns for all stocks in the nasdaq between 2000 and 2018, however the dataset would too large to be ran by my computer, therefore I selected only transportation companies, which, according to wrds-compustat have the NASD Internal Business Description Categories number of 6 — Francesco D'Ignazio, Jun 17 '19 at 21:40
DESCRIBE your source table. I think you will not find this field. — alexherm, Jun 17 '19 at 23:31

score 0 · Answer 1 · answered May 15 '23 at 04:37

The issue is that nsdinx is on a different table (crsp.dse), so needs to be merged in.

library(DBI)
library(dplyr, warn.conflicts = FALSE)

pg <- dbConnect(RPostgres::Postgres())

dbExecute(pg, "SET search_path TO crsp")
#> [1] 0

dsf <- tbl(pg, "dsf")
dse <- tbl(pg, "dse")
dsi <- tbl(pg, "dsi")
nasdin <- 
    dse |>
    filter(!is.na(nsdinx)) |>
    filter(nsdinx == 6) |>
    select(permno, date, event, nsdinx, trtsendt) |>
    rename(trtsdt = date)

dsi |>
    select(date) |>
    inner_join(nasdin, join_by(date >= trtsdt, date <= trtsendt)) |>
    inner_join(dsf) |>
    select(cusip, date, permno, permco, prc, ret, retx, vol, hexcd, nsdinx) 
#> Joining with `by = join_by(date, permno)`
#> # Source:   SQL [?? x 10]
#> # Database: postgres  [igow@/tmp:5432/igow]
#>    cusip    date       permno permco   prc      ret     retx    vol hexcd nsdinx
#>    <chr>    <date>      <int>  <int> <dbl>    <dbl>    <dbl> <int6> <int> <int6>
#>  1 69348J10 1986-03-03  10038   7986 -7    -0.00885 -0.00885  23400     3      6
#>  2 69348J10 1986-03-04  10038   7986 -6.88 -0.0179  -0.0179   83550     3      6
#>  3 69348J10 1986-03-05  10038   7986 -6.81 -0.00909 -0.00909  33875     3      6
#>  4 69348J10 1986-03-06  10038   7986 -6.94  0.0183   0.0183   33500     3      6
#>  5 69348J10 1986-03-07  10038   7986 -6.94  0        0        41735     3      6
#>  6 69348J10 1986-03-10  10038   7986 -7.62  0.0991   0.0991  143024     3      6
#>  7 69348J10 1986-03-11  10038   7986 -8.5   0.115    0.115   159917     3      6
#>  8 69348J10 1986-03-12  10038   7986 -8.75  0.0294   0.0294  156528     3      6
#>  9 69348J10 1986-03-13  10038   7986 -9.31  0.0643   0.0643  240000     3      6
#> 10 69348J10 1986-03-14  10038   7986 -9.69  0.0403   0.0403  204480     3      6
#> # ℹ more rows

^{Created on 2023-05-15 with reprex v2.0.2}

Downloading only transportation companies from the WRDS

1 Answers1