1 of 2
1
What is the difference between company’s primary ticker and ticker? 
Posted: 24 October 2012 08:04 AM   [ Ignore ]
Newbie
Rank
Total Posts:  23
Joined  2012-10-24

Dear Joost,

I extracted some deal data from SDC, but there are two variables that confused me. Could you help me to understand the difference between primary ticker and ticker?
Also, I am trying to check data availability of those deal involved companies on CRSP and Compustat. Normally how do people do that?
I probably asked two dum questions since I am so green.
But it worth trying. Thank you.

pwyw000

Profile
 
 
Posted: 24 October 2012 01:05 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

Dear pwyw000,

In short: I don’t know why there are 2 versions of ‘ticker’ on SDC. But, in general, ticker symbol is not the best choice to use, because ticker symbols are recycled/not stable.

SDC als has cusip (but again, two ‘versions’); cusip is more stable over time.

I am pasting some sas code that I have that will import SDC files (I have exported most but not all of the fields, so maybe you want more variables than I have included).

You can then match on cusip, permno, etc. (I am including these for both the acquiring firm and the target)

best regards,

Joost

%LET dirSDC P:\research\sas_projects\wrds_libs_sas_scripts\sdc platinum;


filename SDC1 "&dirSDC;\ma 1975 - 1979.txt";
filename SDC2 "&dirSDC;\ma 1998 - 1999.txt";
filename SDC3 "&dirSDC;\ma 2000 - 2010.txt";

data work.a_sdc1;
infile SDC1 dsd DELIMITER="" firstobs=8 LRECL=32767 missover;
input annDate_temp 1-8 effDate_temp 14-21 targetName 27-59 acqName 63-98 pctShare 99-104 acqCusip 109-114 acqCusip2 120-125 tgtCusip 131-136 ;
run;

data work.a_sdc2;
infile SDC2 dsd DELIMITER="" firstobs=8 LRECL=32767 missover;
input annDate_temp 1-8 effDate_temp 14-21 targetName 27-69 acqName 71-111 pctShare 113-118 acqCusip 123-128 acqCusip2 135-140 tgtCusip 147-152 ;
run;


data work.a_sdc3;
infile SDC3 dsd DELIMITER="" firstobs=8 LRECL=32767 missover;
input annDate_temp 1-8 effDate_temp 14-21 targetName 27-66 acqName 67-104 pctShare 105-110 acqCusip 115-120 acqCusip2 127-132 tgtCusip 139-144 ;
run;

data work.a_sdc4 (drop year month day annDate_temp effDate_temp);
set  work.a_sdc1  work.a_sdc2  work.a_sdc3;
year substr(annDate_temp72);
month substr(annDate_temp12);
day substr(annDate_temp42);
annDate mdy(monthdayyear);
format annDate date9.;

year substr(effDate_temp72);
month substr(effDate_temp12);
day substr(effDate_temp42);
effDate mdy(monthdayyear);
format effDate date9.;
if 
annDate ne .;
if 
pctShare eq 100;
sdcFlag 1;
run;

proc sort data=work.a_sdc4by tgtCusip annDate;run;

/* Get crsp.dsenames */

data work.a_sdc5 (keep permno NAMEDT NAMEENDT ncusip cusip cusip6 ncusip6);
set crsp.dsenames;
cusip6 substr(cusip16);
ncusip6 substr(ncusip16);
run;

/* Add cusip */

proc sql;

 
create table work.a_sdc6 as
 
select 
  a
.*,
  
b.permno as acqpermno_1
 from
  work
.a_sdc4 a
 LEFT JOIN
  work
.a_sdc5 b
 ON
  a
.acqCusip b.ncusip6
 
and b.NAMEDT <= a.annDate <= b.NAMEENDT;

quit;

proc sql;

 
create table work.a_sdc7 as
 
select 
  a
.*,
  
b.permno as acqpermno_2
 from
  work
.a_sdc6 a
 LEFT JOIN
  work
.a_sdc5 b
 ON
  a
.acqCusip2 b.ncusip6
 
and b.NAMEDT <= a.annDate <= b.NAMEENDT;

quit;


data work.a_sdc8 (keep targetName acqName acqCusip acqCusip2 tgtCusip annDate effDate acqPermno);
set work.a_sdc7;
acqPermno acqpermno_1;
if 
acqPermno eq then acqPermno acqpermno_2;
run;


/* Add gvkey */

proc sql;

  
create table  work.a_sdc9 as
  
select a.*, b.gvkey as acqGvkey
  from 
   work
.a_sdc8 a
  LEFT JOIN
  matching
.ccm b
  ON 
  a
.acqPermno b.lpermno
 
and b.USEDFLAG 1
 
and b.LINKDT <= a.annDate <= b.LINKENDDT 
 
and b.linkprim IN ("C""P");
quit;


/* Add cusip target*/

proc sql;

 
create table work.a_sdc10 as
 
select 
  a
.*,
  
b.permno as tgtPermno
 from
  work
.a_sdc9 a
 LEFT JOIN
  work
.a_sdc5 b
 ON
  a
.tgtCusip b.ncusip6
 
and b.NAMEDT <= a.annDate <= b.NAMEENDT;

quit;


/* Add target gvkey */

proc sql;

  
create table  misc.sdc as
  
select a.*, b.gvkey as tgtGvkey
  from 
   work
.a_sdc10 a
  LEFT JOIN
  matching
.ccm b
  ON 
  a
.tgtPermno b.lpermno
 
and b.USEDFLAG 1
 
and b.LINKDT <= a.annDate <= b.LINKENDDT 
 
and b.linkprim IN ("C""P");
quit;


proc sort data =  misc.sdcby annDaterun

 

 Signature 

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

Profile
 
 
Posted: 24 October 2012 03:12 PM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  23
Joined  2012-10-24

Thank you Joost. Since I have asked, do you think it is possible that you could also upload the structure of data that you got from SDC. It makes it easier that I could understand your codes. smile

Profile
 
 
Posted: 25 October 2012 07:20 AM   [ Ignore ]   [ # 3 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi pwyw000,

I have exported SDC as a ‘flat’ txt file. This is the header of the first file:

Date         Date       Target Name                         Acquiror Name                        of     Acquiror   Acquiror   Target      
Announced    Effective                                                                            Shares     CUSIP     Ultimate   CUSIP
                                                                                                  Acq
.                  Parent
                                                                                                                        CUSIP 

The code in the previous post is referring to the character positions of each of the variables. The three exports had slightly different lengths of the variables, so the positions are different for the 3 files I import.

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: 25 October 2012 08:06 AM   [ Ignore ]   [ # 4 ]
Newbie
Rank
Total Posts:  23
Joined  2012-10-24

Keep adding questions here.
First, In your codes, you metioned “crsp.dsenames, matching.ccm b”. Did you create libraries named “crsp” and “matching” or you remotely retrieved data from crsp or ccm on wrds? I am asking because I did not see you codes for creating libraries.
Second, I am trying to get ccm file from wrds unix server. In your code, which ccm file you were referring to? ccm_lookup?ccm_qvards?
I think these questions can help other green wrds users some rope as well.
Much appreciated Joost.

Best,

pwyw000

Profile
 
 
Posted: 25 October 2012 03:20 PM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi pwyw000,

Good point, yes, I have local downloads of these files.

In fact, my ‘ccm’ is slightly modified from the original. I am posting the code below. (It replaces the ‘missings’ for end date with a future date, this simplifies further usage. See in previous post how the date is required to be between LINKDT and LINKENDDT. If LINKENDDT would be missing - as it is in the ‘original ccm’, it requires more (hard to read) code).

best,

Joost

rsubmit;
libname crsp '/wrds/crsp/sasdata/a_ccm';
   

PROC SQL;
  
create table ccm as
  
select a.*
  
from crsp.ccmxpf_linktable a
   where  a
.lpermno ne .
 and 
a.linktype in ("LC" "LN" "LU" "LX" "LD" "LS")
  ;
  
quit;
 
proc download data=ccm out=matching.ccm_temp;
 
run;
endrsubmit;
 


data matching.ccm;
set matching.ccm_temp;
reset .E to some future date;
if 
LINKENDDT eq .E then LINKENDDT mdy(1,1,2020);

just in case some LINKDT are set to .B;
if 
LINKDT eq .B then LINKDT mdy(1,1,1960);
run;


proc datasets library=matching;
   
delete ccm_temp;
run
 Signature 

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

Profile
 
 
Posted: 01 November 2012 02:28 PM   [ Ignore ]   [ # 6 ]
Newbie
Rank
Total Posts:  23
Joined  2012-10-24

Thanks Joost, I successfully modified your code and obtained the result I needed.
Much appreciated.

pwyw000

Profile
 
 
Posted: 01 November 2012 03:27 PM   [ Ignore ]   [ # 7 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

Great! I am glad it worked out!

smile

 Signature 

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

Profile
 
 
Posted: 14 November 2012 12:12 PM   [ Ignore ]   [ # 8 ]
Newbie
Rank
Total Posts:  23
Joined  2012-10-24

Dear Joost,

I tried to modify variable attributes of sdc merger data and match it with crsp data based on your matching code above. But SAS gave me error report but I could not figure out how to solve it.
Could you help me out here. Much appreciated.

pwyw000

libname
sdc_plm ‘C:\Users\kw367\Documents\My SAS Files\9.3\myfiles\sdc_plm’;

/*Alter variable attributes*/
PROC SQL;
  create table sdc_plm.a_sdc1 as
  select a.Date_Announced ‘annDate’ as annDate,
  a.Date_Effective ‘effDate’ as effDate,
  a.Acquiror_Name ‘acqName’ as acqName,
  a.Acquiror_CUSIP ‘acqCusip’ as acqCusip,
  a.Target_Name ‘targetName’ as targetName,
  a.Target_CUSIP ‘tgtCusip’ as tgtCusip,
  a.Acquiror_Ultimate_Parent_CUSIP ‘acqCusip2’ as acqCusip2,
  a.Target_Ultimate_Parent_CUSIP ‘tgtCusip2’ as tgtCusip2,
  a.Value_of_Transaction___mil_ ‘Dsize’ as Dsize,
  a.Tgt__Market_Val_4_Wwks_Prior_to_ ‘TgtMV’ as TgtMV,
  a.Offer_Price_to_Target_Stock_Pric ‘PM_1db’ as PM_1db,
  a.Offer_Price_to_Target_Stock_Pri0 ‘PM_1wb’ as PM_1wb,
  a.Offer_Price_to_Target_Stock_Pri1 ‘PM_4wb’ as PM_4wb,
  a.Acq_Closing_Price_1_Day_Prior_to ‘acq_prc_1db’ as acq_prc_1db,
  a.Acquiror_Closing_Price_At_Ann___ ‘acq_prc_ann’ as acq_prc_ann,
  a.Acquiror_Closing_Price_1_Day_Aft ‘acq_prc_1da’ as acq_prc_1da,
  a.Target_Share_Price_1_Day_Prior_t ‘tgt_prc_1db’ as tgt_prc_1db,
  a.Target_Closing_Price_At_Ann_Date ‘tgt_prc_ann’ as tgt_prc_ann,
  a.Target_Closing_Price_1_Day_After ‘tgt_prc_1da’ as tgt_prc_1da

  from sdc_plm.deal_96_08_2 a
  ;
  quit;
run;

proc sort data=sdc_plm.a_sdc1; by annDate;run;

/* Get crsp.dsenames */
libname
crsp “&dirDrive;\crsp”;
/*” and ’ here make a difference, can not use ‘’ when using macro “&”*/
data sdc_plm.a_sdc5 (keep = permno NAMEDT NAMEENDT ncusip cusip cusip6 ncusip6);
set crsp.dsenames;
cusip6 = substr(cusip, 1, 6);
ncusip6 = substr(ncusip, 1, 6);
format NAMEDT date9.
  NAMEENDT date9.;
informat NAMEDT date9.
  NAMEENDT date9.;
run;

/* Add cusip */
proc sql;

create table sdc_plm.a_sdc6 as
select
  a.*,
  b.permno as acqpermno_1 /*select column name permno from file b and appear as acqpermno in created table*/

from
  sdc_plm.a_sdc1 a
LEFT JOIN
  sdc_plm.a_sdc5 b
ON
  a.acqCusip = b.ncusip6
and b.NAMEDT <= a.annDate <= b.NAMEENDT;

ERROR: Expression using equals (=) has components that are of different data types.

quit;
run;
....

Profile
 
 
Posted: 14 November 2012 12:20 PM   [ Ignore ]   [ # 9 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi pwyw000,

Most likely ‘annDate’ in SDC is in ‘string’ format. SAS gives an error because it can’t compare a string with a date.

Use code similar to this to turn it into a SAS date:

year substr(annDate72);
month substr(annDate12);
day substr(annDate42);
annDate_s mdy(monthdayyear);
format annDate_s date9.; 

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: 14 November 2012 12:42 PM   [ Ignore ]   [ # 10 ]
Newbie
Rank
Total Posts:  23
Joined  2012-10-24

Dear Joost,

The annDate I imported to SAS has already been in the format of date9. so there is no need for me to convert again.
That is why I could not understand the error message. I checked the variable attributes of NAMEDT, annDate, NAMEENDT and they are all in the date9. format.
I am so confused now.

pwyw000

Profile
 
 
Posted: 14 November 2012 12:49 PM   [ Ignore ]   [ # 11 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi pwyw000,

How about acqCusip vs ncusip6? Same type?

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: 14 November 2012 12:57 PM   [ Ignore ]   [ # 12 ]
Newbie
Rank
Total Posts:  23
Joined  2012-10-24

Good point!
acqCusip is in BEST12., but ncusip6 is a character variable.
I tried to convert ncusip6 using “format ncusip6 BEST12.” but SAS told me that “ERROR 48-59: The format $BEST was not found or could not be loaded.”
Any idea?

Best,

pwyw000

Profile
 
 
Posted: 14 November 2012 01:03 PM   [ Ignore ]   [ # 13 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

numcusip = 1 * ncusip6;

smile

 Signature 

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

Profile
 
 
Posted: 14 November 2012 01:18 PM   [ Ignore ]   [ # 14 ]
Newbie
Rank
Total Posts:  23
Joined  2012-10-24

Gs….How could I forgot…..
Thank you so much Joost.

pwyw000

Profile
 
 
Posted: 14 November 2012 02:20 PM   [ Ignore ]   [ # 15 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

you’re welcome—good luck!

 Signature 

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

Profile
 
 
   
1 of 2
1