Anybody merged Dealscan with Compustat here ??
Posted: 02 January 2015 04:53 PM   [ Ignore ]
Newbie
Rank
Total Posts:  16
Joined  2014-04-04

It seems this link file is the way to go. 

http://finance.wharton.upenn.edu/~mrrobert/styled-9/styled-12/index.html

anybody has some SAS code to do the job??

I am new to SAS. Any help is appreciated.

thanks!

Profile
 
 
Posted: 02 January 2015 08:15 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Clark,

What is the difficulty? It looks like you need to import the excel file into sas (Google: SAS import Excel), and then match it on gvkey (Google: proc sql, inner join). After matching on gvkey, there may be doubles to deal with (hard to tell up front).

best regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 12 January 2015 01:54 PM   [ Ignore ]   [ # 2 ]
Sr. Member
RankRankRankRank
Total Posts:  169
Joined  2011-09-20

*import excel into SAS;

PROC IMPORT OUT = dclink
        DATAFILE= “S:\wrds_libs\Dealscan\dealscanlink2012.csv”
        DBMS = CSV REPLACE;
    GETNAMES= YES;  /*get column names*/
    DATAROW= 2   ; /*the first row is names, the 2nd row is real data*/
    GUESSINGROWS=32767; /*this is the max number of guessing rows, very important, may import trancated names if we dont use this feature*/
RUN;

 Signature 

Zenghui
A humble student of business

Profile
 
 
Posted: 12 January 2015 01:56 PM   [ Ignore ]   [ # 3 ]
Sr. Member
RankRankRankRank
Total Posts:  169
Joined  2011-09-20

* merge dealscan data together;
*don’t be scared to see this long code. it is very easy, Enjoy the code;


libname ds ‘S:\wrds_libs\Dealscan\Dealscan’;

*load facility data;

data facility;
set ds.facility;
run;

proc sort data = facility nodupkey;
by facilityid ;
run;

proc sort data = facility nodupkey;
by facilityid facilitystartdate;
run;

/*
Unique Facility ID = facilty id
there are 249,451 obs in this data
no dup found from this data
*/

data base1;
  set facility;
  facAmtm=FacilityAmt/1000000; /*million dollars*/
  keep facilityid packageid facilitystartdate facilityenddate facamtm currency exchangerate company borrowercompanyid loantype primarypurpose countryofsyndication maturity secured distributionmethod
    seniority countryofsyndication;
run;

/*base1 file = simplified facility*/

* load lendershares file;
data lendershares;
set ds.lendershares;
run;

proc sort data = lendershares;
by FacilityID;
run;


proc sort data = lendershares nodupkey out = lender2 dupout = dupout;
by FacilityID CompanyID Lender ;
run;


/*
facility ID
company ID = lender company ID
each facility could have multi lender.
delete 52 dup of LenderRole in 1,378,815 obs from the above step;
*/

*merge lender info onto the facility ;

*Add lender information;
  proc sql;
  create table with_lender as
  select a.*, b.lender, b.lenderrole,b.bankallocation,b.leadarrangercredit, b.agentcredit, b.companyid as lender_companyid
  from base1 as a left join
      lender2 as b
  on a.facilityid=b.facilityid;
  quit;

data with_lender2;
set with_lender;
rename company = borrower;
label
company = borrower
lender_companyid = lender_companyid;
run;


*take a look at with_lender2;

proc sort data = with_lender2 nodupkey out = with_lender3 dupout = dupout2;
by FacilityID lender_companyid;
run;

/*
1,381,754 observations
each obs = facility info + lender info

no duplication is found with both facilityid and lender company id;
*/

*Add company information;

*the company file include both lender and borrower company informaiton;
*not historical, only have the most recent info;

data dealscan_companies;
set ds.company;
run;

proc sort data = dealscan_companies nodupkey;
by CompanyID;
run;


proc sql;
  create table with_comp_info1 as
  select a.*, b.country as borrower_country, b.primarysiccode as borrower_siccode
  from with_lender3 as a left join
      dealscan_companies as b
  on a.borrowercompanyid = b.companyid;
  quit;


*add lender company info;
proc sql;
  create table with_comp_info2 as
  select a.*, b.country as lender_country, b.primarysiccode as lender_siccode, b.institutiontype as lender_InstiType
  from with_comp_info1 as a left join
      dealscan_companies as b
  on a.lender_companyid = b.companyid;
  quit;


data with_comp_info3;
set with_comp_info2;
label
lender_country = lender_country
lender_siccode = lender_siccode
borrower_country = borrower_country
borrower_siccode = borrower_siccode
lender_InstiType=“Lender’s Institution Type”;
run;


*add interest rate;

*Add base rate to the combined dataset, restricted base rate to be on LIBOR;
  data currfacpricing;
    set ds.currfacpricing;
    run;

  data baserate;
  set currfacpricing;
  KEEP facilityid baserate minbps maxbps;
    if baserate=“LIBOR”;
  run;

*Add base rate;
proc sql;
  create table with_rate as
  select a.*, b.baserate, b.minbps,b.maxbps
  from with_comp_info2 as a left join
      baserate as b
  on a.facilityid=b.facilityid;
  quit;


*add package amount;

  data package;
set ds.package;
run;


  proc sql;
  create table with_dealamt as
  select a.*, b.dealamount/1000000 as DealAmountM
  from with_rate as a left join
      package as b
  on a.packageid=b.packageid;
  quit;

/*no dup here*/


proc sort data=with_dealamt out=with_dealamt2 dupout=dups nodupkey;
by facilityid lender lender_SicCode lenderrole bankallocation;
run; /* 0 duplicates observations were deleted*/


* merge gvkey to the borrowers companies;

libname dslink ‘S:\wrds_libs\Dealscan’;

*use link file to load the borrower gvkey;

proc sql;
  create table with_gvkey as
  select a.*, b.gvkey as borrower_gvkey ,b.coname_h
  from with_dealamt2 as a left join
      dslink.link2012 as b
  on a.FacilityID = b.facid
  and
  a.FacilityStartDate = b.facstartdate
  and
  a.BorrowerCompanyID=b.bcoid
;
quit;

 

 Signature 

Zenghui
A humble student of business

Profile
 
 
Posted: 12 January 2015 02:05 PM   [ Ignore ]   [ # 4 ]
Sr. Member
RankRankRankRank
Total Posts:  169
Joined  2011-09-20

Hi Joost,

I didn’t use inner join….If there any mistake in my code. let me know. Thanks!

Hi Clark,

The overall picture is: ne borrower per package, and the link file provide gvkeys for these borrowers; there are typically multi facilities per package, and multi lenders per facility.

I not sure if the link file works for the lenders (since they are banks). If you need that, play with the above code, and merge by the company ID to gvkey (use the same link file), See if that will give you some matches.

Good luck!

 Signature 

Zenghui
A humble student of business

Profile
 
 
Posted: 12 January 2015 06:49 PM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

thanks, Zenghui!

best,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile