/* xref.sas 

Sample program merging CRSP and Compustat data kindly provided by:

Robert Sorrels			Research Computing
School of Business		U of W

who stated:

  This is the fairly crude hack we put together to provide a cross
  reference between CRSP and Compustat.  It is important to note that we
  have not done a systematic validation on the results of this file -
  though several of my users have reported that the matches correlate
  well with other data they have - so a user beware caution is in order.

Robert may call it "a crude hack" but it looks pretty impressive to me.
This header and formatting in the program added by Don Cram, who hopes
he hasn't introduced any errors.  -- Don 7/28/97

*/

%include includes;
/* This sources a standard set of libs and macros */

libname crcp '~/dissertation/data';
libname nx '/bulk/bacs3/nasdaq';
libname banka '/bulk/bacs/bacs0/banka/banka';

options nocenter;

data all;	
    merge bicar.master(rename = (dnum = dnum)
	               in=inbicar keep = cnum cic coname smbl dnum) 
	bica.master(rename = (dnum = dnum5)
		       in=inbica keep = cnum cic coname smbl dnum)
	banka.master(rename = (dnum = dnum4) 
		       in=inbank keep = cnum cic coname smbl dnum)
	pde.master(rename = (dnum = dnum3)
		       in=inpde keep = cnum cic coname smbl dnum)
        icq.master(rename = (dnum = dnum2)
                       in=inicq keep = cnum cic coname smbl dnum)
        ica.master(rename = (dnum = dnum1)
                       in=inica keep = cnum cic coname smbl dnum)
        icar.master(rename = (dnum = dnum7)
                       in=inicar keep = cnum cic coname smbl dnum);
    by cnum cic;
    array dnums{*} dnum1 - dnum7;
    do i = 1 to dim(dnums);
	if dnums{i} ne '' then dnum = dnums{i};
	end;                                            
    if inica then in_ica = 'X';
    if inicar then in_icar = 'X';
    if inbicar then in_bicar = 'X';
    if inbica then in_bica = 'X';
    if inicq then in_icq = 'X';
    if inbank then in_bank = 'X';
    if inpde then in_pde = 'X';
run;

proc sql;
  create table xrefnx1 as
  select a.cnum, a.cic, a.coname, a.smbl, a.in_ica, a.in_icar,
         a.in_bicar, a.in_bica, a.in_bank, a.in_pde,
         a.dnum, b.permno
   from all as a, nx.names as b
   where ((a.cnum = (substr(b.ncusip,1,6))) and
          ((substr(a.cic,1,2)) = (substr(b.ncusip,7,2)))  ) ;

proc sort data=xrefnx1; 
    by cnum cic;

data xrefnx1; 
    set xrefnx1; 
    issue = 'y';

data xrefnx1; 
    set xrefnx1; 
    by cnum cic;
    if first.cnum and first.cic then output; else delete;

proc sql;  
    create table xrefnx2 as
	select a.cnum, a.cic, a.coname, a.smbl, a.in_ica, a.in_icar,
	a.in_bicar, a.in_bica, a.in_bank, a.in_pde,
	a.dnum, b.permno
	from all as a, nx.names as b
	where ((a.cnum = (substr(b.ncusip,1,6))) and
		   ((substr(a.cic,1,2)) ne (substr(b.ncusip,7,2))) ) ;

proc sort data=xrefnx2;
    by cnum cic;

data xrefnx2; 
    set xrefnx2; 
    issue = 'n';

data xrefnx2; 
  set xrefnx2; 
  by cnum cic;
  if first.cnum and first.cic then output; else delete;

proc sql;
    create table xrefdx1 as
	select a.cnum, a.cic, a.coname, a.smbl, a.in_ica, a.in_icar,
         a.in_bicar, a.in_bica, a.in_bank, a.in_pde,
	 a.dnum, b.permno
	 from all as a, dx.names as b
	 where ((a.cnum = (substr(b.ncusip,1,6))) and
          ((substr(a.cic,1,2)) = (substr(b.ncusip,7,2))) ) ;

proc sort data=xrefdx1; 
    by cnum cic;

data xrefdx1; 
    set xrefdx1; 
    issue = 'y';

data xrefdx1; 
    set xrefdx1; 
    by cnum cic;
    if first.cnum and first.cic then output; else delete;

proc sql;
    create table xrefdx2 as
	select a.cnum, a.cic, a.coname, a.smbl, a.in_ica, a.in_icar,
	a.in_bicar, a.in_bica, a.in_bank, a.in_pde,
	a.dnum, b.permno                                                       
	from all as a, dx.names as b
   where ((a.cnum = (substr(b.ncusip,1,6))) and
          ((substr(a.cic,1,2)) ne (substr(b.ncusip,7,2))) ) ;

proc sort data=xrefdx2; 
    by cnum cic;

data xrefdx2;
    set xrefdx2; issue = 'n';

data xrefdx2;
    set xrefdx2; 
    by cnum cic;
    if first.cnum and first.cic then output; else delete;

data alltab;
    set xrefnx1 (in = a1) xrefnx2 (in = a2)
	xrefdx1 (in = a3) xrefdx2 (in = a4);
    if a1 then code = 'NX-Y'; /* Nasdaq - issue agreed */
    if a2 then code = 'NX-N'; /* Nasdaq - issue not agreed */
    if a3 then code = 'DX-Y'; /* NYSE/AMEX - issue agreed */
    if a4 then code = 'DX-N'; /* NYSE/AMEX - issue not agreed */
run;

proc sql;
    create table crcp.xreftab as
	select distinct * from alltab
	order by coname;

endsas;