Changes

Jump to navigation Jump to search
* EXCHCD
* SICCD
*NCUSIP *TICKER *COMNAM*NAICS*PRIMEXCH*PERMCO*CUSIP*PRC*VOL*RET*SHR*OUT*NUMTRD*vwretdPE*ewretd
CREATE TABLE CRSP (
PermNo int,
date date,
EXcode int,
SIC int,
NCUSIP varchar(10),
Ticker varchar(10),
Name varchar(100),
NAICS int,
PrimEx varchar(1),
PERMCO int,
CUSIP varchar(12),
Price real,
VOL int,
RET real,
ShOut int,
trade int,
EQRET real,
VRET real);
The data file, crsp-oct10.txt, has a header in the first line. I couldn't find an argument for copy from function to account for this. So, I'll just delete the first line from the file and try again to copy the data into the crsp table.
 
tail -n +2 CRSP-oct10.txt > CRSP-oct10NH.stdout
 
I renamed the output to CRSP-NH, NH for no header. Let's take a look if the header is actually removed.
 
head -2 CRSP-NH.txt
 
output:
10000 1986/01/06 7952 68391610 -0.000138 0.001926
10000 1986/01/07 3 3990 68391610 OMFGA OPTIMUM MANUFACTURING INC Q 7952 68391610 -2.56250 1000 C 3680 0.013809 0.011061
 
Now, let's copy the data from CRSP-NH.txt into the CRSP table.
 
\COPY crsp FROM '/bulk/WinnersCurse/CRSP-NH.txt';
ERROR: invalid input syntax for integer: ""
CONTEXT: COPY crsp, line 1, column excode: ""
 
To avoid problems such the one above, I'll just copy all the variables as varchar.
 
 
CREATE TABLE CRSPtmp (
PermNo varchar(10),
date date,
EXcode varchar(10),
SIC varchar(10),
NCUSIP varchar(10),
Ticker varchar(10),
Name varchar(100),
NAICS varchar(10),
PrimEx varchar(1),
PERMCO varchar(10),
CUSIP varchar(12),
Price varchar(15),
VOL varchar(10),
RET varchar(10),
ShOut varchar(10),
trade varchar(10),
EQRET varchar(10),
VRET varchar(10));
\COPY CRSPtmp FROM '/bulk/WinnersCurse/CRSP-NH.txt';
COPY 64614281
 
SELECT * FROM CRSPtmp limit 10;
 
permno | date | excode | sic | ncusip | ticker | name | naics | primex | permco | cusip | price | vol | ret | shout | trade | eqret | vret
--------+------------+--------+------+----------+--------+---------------------------+-------+--------+--------+----------+----------+-------+-----------+-------+-------+-----------+-----------
10000 | 1986-01-06 | | | | | | | | 7952 | 68391610 | | | | | | -0.000138 | 0.001926
10000 | 1986-01-07 | 3 | 3990 | 68391610 | OMFGA | OPTIMUM MANUFACTURING INC | | Q | 7952 | 68391610 | -2.56250 | 1000 | C | 3680 | | 0.013809 | 0.011061
10000 | 1986-01-08 | 3 | 3990 | 68391610 | OMFGA | OPTIMUM MANUFACTURING INC | | Q | 7952 | 68391610 | -2.50000 | 12800 | -0.024390 | 3680 | | -0.020744 | -0.005117
10000 | 1986-01-09 | 3 | 3990 | 68391610 | OMFGA | OPTIMUM MANUFACTURING INC | | Q | 7952 | 68391610 | -2.50000 | 1400 | 0.000000 | 3680 | | -0.011219 | -0.011588
10000 | 1986-01-10 | 3 | 3990 | 68391610 | OMFGA | OPTIMUM MANUFACTURING INC | | Q | 7952 | 68391610 | -2.50000 | 8500 | 0.000000 | 3680 | | 0.000083 | 0.003651
10000 | 1986-01-13 | 3 | 3990 | 68391610 | OMFGA | OPTIMUM MANUFACTURING INC | | Q | 7952 | 68391610 | -2.62500 | 5450 | 0.050000 | 3680 | | 0.002749 | 0.002433
10000 | 1986-01-14 | 3 | 3990 | 68391610 | OMFGA | OPTIMUM MANUFACTURING INC | | Q | 7952 | 68391610 | -2.75000 | 2075 | 0.047619 | 3680 | | 0.000366 | 0.004474
10000 | 1986-01-15 | 3 | 3990 | 68391610 | OMFGA | OPTIMUM MANUFACTURING INC | | Q | 7952 | 68391610 | -2.87500 | 22490 | 0.045455 | 3680 | | 0.008206 | 0.007693
10000 | 1986-01-16 | 3 | 3990 | 68391610 | OMFGA | OPTIMUM MANUFACTURING INC | | Q | 7952 | 68391610 | -3.00000 | 10900 | 0.043478 | 3680 | | 0.004702 | 0.005670
10000 | 1986-01-17 | 3 | 3990 | 68391610 | OMFGA | OPTIMUM MANUFACTURING INC | | Q | 7952 | 68391610 | -3.00000 | 8470 | 0.000000 | 3680 | | -0.001741 | 0.003297
(10 rows)
==Previous Papers==

Navigation menu