This program shows the main processing part of the CCM web queries.
Background
XpressFeed version CCM product provides more detailed information on linking history, in the meanwhile it also populates much more observations than the old version, among which there are some consecutive records (consecutive linking date range) with the same GVKEY/IID/PERMNO pair but different LINKTYPE or USEDFLAG or LINKPRIM value. This program will collapse such consecutive rows and combine them into one.
Example
/**********************************************************************************
* Program : ccm_lnktable.sas *
* Author : WRDS/M. Duan *
* Date Created : April 2009 *
* *
* Usage : *
* This program shows the main processing part of the CCM web queries. *
* XpressFeed version CCM product provides more detailed information on linking *
* history, in the meanwhile it also populates much more observations than the old *
* version, among which there are some consecutive records (consecutive linking *
* date range) with the same GVKEY/IID/PERMNO pair but different LINKTYPE or *
* USEDFLAG or LINKPRIM value. This program will collapse such consecutive rows *
* and combine them into one. *
* An example might be helpful: *
* Two records can be found in the link table (ccmxpf_linktable): *
* GVKEY LPERMNO LINKDT LINKENDDT LINKPRIM LIID LINKTYPE LPERMCO USEDFLAG *
* 001043 18980 19490801 19581230 C 00X LN 20009 -1 *
* 001043 18980 19581231 19620130 C 00X LC 20009 1 *
* After this program, there is only one line left: *
* 001043 18980 19490801 19620130 C 00X LC 20009 1 *
* *
* Notes: *
* Include LIID in all the BY statements ONLY IF the LNK table is to be used *
* to merge with Security Monthly table. Otherwise, LIID should be removed from *
* the BY statements. *
***********************************************************************************/
/* if using SAS Connect, use the following:
libname mylocal 'C:\CRSP\Data';
%let wrds = wrds.wharton.upenn.edu 4016;
options comamid=TCP remote=wrds;
signon username=_prompt_;
rsubmit;
*/
libname lib '/wrds/crsp/sasdata/cc';
/* ---------------------------------------------------------------- */
/* create the "andlu" macro variable that indicates if the usedflag */
/* = 1 links will be used exclusively or not. */
/* Set to %str( ) if using both usedflag=1 or -1. */
/* ---------------------------------------------------------------- */
%let andlu = %str(usedflag = 1);
/* ---------------------------------------------------------------- */
/* create the "andlt" macro variable that will keep only the */
/* desired linktypes. */
/* ---------------------------------------------------------------- */
%let andlt = %str(linktype in ("LC" "LN" "LU" "LX" "LD" "LS"));
/* ---------------------------------------------------------------- */
/* List of GVKEYs */
/* ---------------------------------------------------------------- */
%let glist = '001043' '006066' '012141' '014489';
/* ---------------------------------------------------------------- */
/* Create LNK table by selecting rows with desired GVKEYs and date */
/* range. */
/* ---------------------------------------------------------------- */
proc sql;
create table lnk1 as select *
from lib.ccmxpf_linktable
where gvkey in (&glist) and &andlu and &andlt
order by gvkey, lpermno, lpermco, linkdt, linkenddt
;
quit;
/* ---------------------------------------------------------------- */
/* Processing LNK table to collapse */
/* ---------------------------------------------------------------- */
data lnk2;
set lnk1;
by gvkey lpermno lpermco linkdt linkenddt;
format prev_ldt prev_ledt yymmddn8.;
retain prev_ldt prev_ledt;
if first.lpermno then do;
if last.lpermno then do;
/* Keep this obs if it's the first and last matching permno pair */
output;
end;
else do;
/* If it's the first but not the last pair, retain the dates for future use */
prev_ldt = linkdt;
prev_ledt = linkenddt;
output;
end;
end;
else do;
if linkdt=prev_ledt+1 or linkdt=prev_ledt then do;
/* If the date range follows the previous one, assign the previous linkdt value
to the current - will remove the redundant in later steps. Also retain the
link end date value */
linkdt = prev_ldt;
prev_ledt = linkenddt;
output;
end;
else do;
/* If it doesn't fall into any of the above conditions, just keep it and retain the
link date range for future use*/
output;
prev_ldt = linkdt;
prev_ledt = linkenddt;
end;
end;
drop prev_ldt prev_ledt;
run;
data lnk;
set lnk2;
by gvkey lpermno linkdt;
if last.linkdt;
/* remove redundant observations with identical LINKDT (result of the previous data step), so that
each consecutive pair of observations will have either different GVKEY-IID-PERMNO match, or
non-consecutive link date range
*/
run;
proc print data=lnk;
run;
proc download data=lnk out=mylocal.lnk;
run;
/*
endrsubmit;
signoff;
*/
/* ********************************************************************************* */
/* ************* Material Copyright Wharton Research Data Services *************** */
/* ****************************** All Rights Reserved ****************************** */
/* ********************************************************************************* */