From dave@keynes.bryant.edu Fri Jun 28 14:18:40 1996 Received: from research1.bryant.edu (research1.bryant.edu [192.124.153.2]) by gsb-pound.Stanford.EDU (8.7.5/8.7.1) with SMTP id OAA00454 for ; Fri, 28 Jun 1996 14:18:36 -0700 (PDT) Received: by research1.bryant.edu (5.65/DEC-Ultrix/4.3) id AA00213; Fri, 28 Jun 1996 17:18:38 -0400 Date: Fri, 28 Jun 1996 17:18:32 -0400 (EDT) From: David Louton To: doncram@gsb-crown.stanford.edu, jfranklin@csom.umn.edu Subject: SAS routine to convert CRSP indices to SAS datasets Message-Id: Mime-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII Status: RO Sorry about the delay in getting back to you with these routines. On looking at them again, I noticed that I have not included a portion for the REBAL files which were recently added to the CRSP Indices product. There has not been much demand for that data at our site, so I put off making those adjustments the routines. In that sense the attached files could be considered incomplete. ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + David Louton Phone: (401) 232-6343 + + Assistant Professor Fax: (401) 232-6319 + + Department of Finance Email: dave@keynes.bryant.edu + + Bryant College WWW: http://keynes.bryant.edu/~dave + + 1150 Douglas Pike + + Smithfield, RI 02917-1284 + ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ + "I bet with my net I will get those things yet" --- Dr. Seuss + ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Here is the SAS routine for making datasets out of the CRSP Indices. As you can see it is very repetitive and requires only minor editing from year to year. By giving the variables names that closely match the names used in the manual for the indices product it is very easy for users to find what they want: options ps=60 ls=80; libname index '/data/crsp/indices'; data index.nyse_d; infile '/data/crsp/indices/dsia.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx sprtrn spindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.nyse_m; infile '/data/crsp/indices/msia.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx sprtrn spindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.nyse_q; infile '/data/crsp/indices/qsia.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx sprtrn spindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.nyse_a; infile '/data/crsp/indices/asia.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx sprtrn spindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.amex_d; infile '/data/crsp/indices/dsib.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx sprtrn spindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.amex_m; infile '/data/crsp/indices/msib.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx sprtrn spindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.amex_q; infile '/data/crsp/indices/qsib.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx sprtrn spindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.amex_a; infile '/data/crsp/indices/asib.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx sprtrn spindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.nyam_d; infile '/data/crsp/indices/dsic.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx sprtrn spindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.nyam_m; infile '/data/crsp/indices/msic.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx sprtrn spindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.nyam_q; infile '/data/crsp/indices/qsic.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx sprtrn spindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.nyam_a; infile '/data/crsp/indices/asic.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx sprtrn spindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.nyam_bet; infile '/data/crsp/indices/dsbc.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx sprtrn spindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.nyam_std; infile '/data/crsp/indices/dssc.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx sprtrn spindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.nasdaq_d; infile '/data/crsp/indices/dsio.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx ncrtrn ncindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.nasdaq_m; infile '/data/crsp/indices/msio.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx ncrtrn ncindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.nasdaq_q; infile '/data/crsp/indices/qsio.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx ncrtrn ncindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.nasdaq_a; infile '/data/crsp/indices/asio.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx ncrtrn ncindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.nyamns_d; infile '/data/crsp/indices/dsix.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx sprtrn spindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.nyamns_m; infile '/data/crsp/indices/msix.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx sprtrn spindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.nyamns_q; infile '/data/crsp/indices/qsix.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx sprtrn spindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; data index.nyamns_a; infile '/data/crsp/indices/asix.95c' lrecl=466; input caldt vwretd vwindd vwretx vwindx ewretd ewindd ewretx ewindx sprtrn spindx decret1 decind1 decret2 decind2 decret3 decind3 decret4 decind4 decret5 decind5 decret6 decind6 decret7 decind7 decret8 decind8 decret9 decind9 decret10 decind10 totval totcnt usdval usdcnt; run; Here is the routine for doing the same sort of conversion on the SBBI data included on the indices tape. I suppose I could combine the two into one file: options ps=60 ls=80; libname index '/data/crsp/indices'; data index.sbbi_m; infile '/data/crsp/indices/msbbi.95c' lrecl=288; input caldt cstret cstind csiret csiind cscret cscind gbtret gbtind gbiret gbiind gbcret gbcind cbtret cbtind ustret ustind cpiret cpiind sstret sstind; data index.sbbi_q; infile '/data/crsp/indices/qsbbi.95c' lrecl=288; input caldt cstret cstind csiret csiind cscret cscind gbtret gbtind gbiret gbiind gbcret gbcind cbtret cbtind ustret ustind cpiret cpiind sstret sstind; data index.sbbi_a; infile '/data/crsp/indices/asbbi.95c' lrecl=288; input caldt cstret cstind csiret csiind cscret cscind gbtret gbtind gbiret gbiind gbcret gbcind cbtret cbtind ustret ustind cpiret cpiind sstret sstind; run; endsas; This is just the Unix shell script that I use to move the files from tape to disk: dd if=/dev/nrmt0h of=copyright.95 ibs=80 cbs=80 conv=unblock dd if=/dev/nrmt0h of=chdec.for ibs=4000 cbs=80 conv=unblock dd if=/dev/nrmt0h of=bidec.for ibs=4000 cbs=80 conv=unblock dd if=/dev/nrmt0h of=chcap.for ibs=4000 cbs=80 conv=unblock dd if=/dev/nrmt0h of=bicap.for ibs=4000 cbs=80 conv=unblock dd if=/dev/nrmt0h of=chsp.for ibs=4000 cbs=80 conv=unblock dd if=/dev/nrmt0h of=bisp.for ibs=4000 cbs=80 conv=unblock dd if=/dev/nrmt0h of=chsbbi.for ibs=4000 cbs=80 conv=unblock dd if=/dev/nrmt0h of=bisbbi.for ibs=4000 cbs=80 conv=unblock dd if=/dev/nrmt0h of=dsia.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=msia.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=qsia.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=asia.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=dsib.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=msib.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=qsib.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=asib.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=dsic.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=msic.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=qsic.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=asic.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=dsio.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=msio.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=qsio.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=asio.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=dsix.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=msix.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=qsix.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=asix.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=dsbc.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=dssc.95c ibs=4660 cbs=466 conv=unblock dd if=/dev/nrmt0h of=mhistn.95c ibs=4600 cbs=92 conv=unblock dd if=/dev/nrmt0h of=mhista.95c ibs=4600 cbs=92 conv=unblock dd if=/dev/nrmt0h of=mhistq.95c ibs=4600 cbs=92 conv=unblock dd if=/dev/nrmt0h of=rebaln.95c ibs=4000 cbs=100 conv=unblock dd if=/dev/nrmt0h of=rebala.95c ibs=4000 cbs=100 conv=unblock dd if=/dev/nrmt0h of=rebalq.95c ibs=4000 cbs=100 conv=unblock dd if=/dev/nrmt0h of=dsp500.95c ibs=3900 cbs=130 conv=unblock dd if=/dev/nrmt0h of=msp500.95c ibs=3900 cbs=130 conv=unblock dd if=/dev/nrmt0h of=msbbi.95c ibs=2880 cbs=288 conv=unblock dd if=/dev/nrmt0h of=qsbbi.95c ibs=2880 cbs=288 conv=unblock dd if=/dev/nrmt0h of=asbbi.95c ibs=2880 cbs=288 conv=unblock Finally, here is an example of a short routine used to access the data: options ps=60 ls=80; libname index '/data/crsp/indices'; data one; set index.nyse_m; /* Select the variables you wish to keep */ /* i.e. the following lines keep calendar date and vwretd */ /* and then write them to an output file */ keep caldt vwretd; if caldt lt '620700' then delete; /* DO YOUR ANALYSIS HERE */ run;