This example shows how to extract data from a 48-quarter Compustat Database File. For COMPUSTAT data files, the series variable names are constructed by concatenating the name of the data array DATA and the column number containing the required information. For example, for quarterly files the common stock data is in column 56. Therefore, the variable name for this series is DATA56. Similarly, the series variable names for quarterly footnotes are constructed by adding the column number to the array name, QFTNT. For example, the variable name for common stock footnotes is QFTNT14 since the 14th column of the QFTNT array contains this information.
The following example extracts common stock series (DATA56) and its footnote (QFTNT14) for companies whose stocks are traded over-the-counter and not in the S&P 500 Index (ZLIST=06) and whose data reside in the over-the-counter file (FILE=06).
filename compstat "%sysget(DATASRC_DATA)csqibm.dat" recfm=s370v lrecl=4820 blksize=14476; proc datasource filetype=cs48qibm infile=compstat out=stocks outby=company; keep data56 qftnt14; rename data56=comstock qftnt14=ftcomstk; label data56='Common Stock' qftnt14='Footnote for Common Stock'; range from 1990:4; run; /*- add company name to the out= data set */ data stocks; merge stocks company( keep=dnum cnum cic coname ); by dnum cnum cic; run; title1 'Common Stocks for Last Quarter of 1990'; proc print data=stocks ; run;
Output 12.7.1 contains a listing of the STOCKS data set.
Output 12.7.1: Listing of the OUT=STOCKS Data Set
Common Stocks for Last Quarter of 1990 |
Obs | DNUM | CNUM | CIC | FILE | EIN | STK | SMBL | ZLIST | XREL | FIC | INCORP | STATE | COUNTY | DATE | comstock | ftcomstk | CONAME |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 2670 | 293308 | 102 | 6 | 56-0481457 | 0 | ENGH | 6 | 0 | 0 | 10 | 13 | 121 | 1990:4 | 16.2510 | ENGRAPH INC | |
2 | 2835 | 372917 | 104 | 6 | 06-1047163 | 0 | GENZ | 6 | 0 | 0 | 10 | 25 | 17 | 1990:4 | 0.1620 | GENZYME CORP | |
3 | 3564 | 896726 | 106 | 6 | 25-0922753 | 0 | TRON | 6 | 0 | 0 | 42 | 37 | 105 | 1990:4 | 3.1380 | TRION INC | |
4 | 3576 | 172755 | 100 | 6 | 77-0024818 | 0 | CRUS | 6 | 0 | 0 | 6 | 6 | 85 | 1990:4 | . | CIRRUS LOGIC INC | |
5 | 3577 | 602191 | 108 | 6 | 11-2693062 | 0 | MILT | 6 | 0 | 0 | 10 | 36 | 103 | 1990:4 | . | MILTOPE GROUP INC | |
6 | 3630 | 616350 | 104 | 6 | 34-0299600 | 0 | MORF | 6 | 0 | 0 | 39 | 39 | 35 | 1990:4 | . | MOR-FLO INDS | |
7 | 3674 | 827079 | 203 | 6 | 94-1527868 | 0 | SILI | 6 | 0 | 0 | 10 | 6 | 85 | 1990:4 | . | SILICONIX INC | |
8 | 3842 | 602720 | 104 | 6 | 25-0668780 | 0 | MNES | 6 | 0 | 0 | 42 | 42 | 3 | 1990:4 | 6.7540 | MINE SAFETY APPLIANCES CO | |
9 | 5080 | 007698 | 103 | 6 | 59-1001822 | 0 | AESM | 6 | 0 | 0 | 12 | 12 | 25 | 1990:4 | . | AERO SYSTEMS INC | |
10 | 5122 | 090324 | 104 | 6 | 84-0601662 | 0 | BIND | 6 | 0 | 0 | 18 | 18 | 97 | 1990:4 | 3.2660 | BINDLEY WESTERN INDS | |
11 | 5211 | 977865 | 104 | 6 | 38-1746752 | 0 | WLHN | 6 | 0 | 0 | 26 | 26 | 145 | 1990:4 | 6.4800 | WOLOHAN LUMBER CO | |
12 | 5600 | 299155 | 101 | 6 | 36-1050870 | 0 | EVAN | 6 | 0 | 0 | 10 | 17 | 31 | 1990:4 | . | EVANS INC | |
13 | 5731 | 382091 | 106 | 6 | 94-2366177 | 0 | GGUY | 6 | 0 | 0 | 6 | 6 | 75 | 1990:4 | 0.0520 | GOOD GUYS INC | |
14 | 7372 | 45812M | 104 | 6 | 94-2658153 | 0 | INTS | 6 | 0 | 0 | 6 | 6 | 85 | 1990:4 | . | INTEGRATED SYSTEMS INC | |
15 | 7372 | 566140 | 109 | 6 | 04-2711580 | 0 | MCAM | 6 | 0 | 0 | 25 | 25 | 17 | 1990:4 | 0.0770 | MARCAM CORPORATION | |
16 | 7373 | 913077 | 103 | 6 | 81-0422894 | 0 | TOTE | 6 | 0 | 0 | 10 | 30 | 111 | 1990:4 | 0.0570 | UNITED TOTE INC | |
17 | 7510 | 008450 | 108 | 6 | 34-1050582 | 0 | AGNC | 6 | 0 | 0 | 10 | 39 | 35 | 1990:4 | . | AGENCY RENT-A-CAR INC | |
18 | 7819 | 026038 | 307 | 6 | 23-2359277 | 0 | AFTI | 6 | 0 | 0 | 10 | 42 | 45 | 1990:4 | 0.0210 | AMERICAN FILM TECHNOL | |
19 | 8700 | 055383 | 103 | 6 | 59-1781257 | 0 | BEIH | 6 | 0 | 0 | 10 | 13 | 121 | 1990:4 | 0.5170 | BEI HOLDINGS LTD | |
20 | 8731 | 759916 | 109 | 6 | 04-2729386 | 0 | RGEN | 6 | 0 | 0 | 10 | 25 | 17 | 1990:4 | . | REPLIGEN CORP |
Note that quarterly Compustat data are also available in Universal Character format. If you have this type of file instead of IBM 360/370 General format, use the FILETYPE=CS48QUC option instead.