Example 12.7 Quarterly COMPUSTAT Data Files

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.