Getting GVKEY from list of company fkeys
Posted: 27 May 2014 10:15 AM   [ Ignore ]
Newbie
Rank
Total Posts:  3
Joined  2014-05-21

Hello,

I have a list of company fkeys from Audit Analytics.  I would like to get a matching column with gvkeys so I can later calculate accruals using compustat.  When I use the code below my sample goes from 22940 observations to 50,867 observations.  Could anyone tell me why the number of observations is increasing?  Thank you for any help you can offer! grin


proc sql;
create table MW_404 as
select distinct b.company_fkey, b.FY_IC_OP, b.COUNT_WEAK, b.FYE_IC_OP, b.IC_OP_TYPE, b.IS_NTH_RESTATE, b.FILE_DATE, b.FORM_FKEY
from aa.auditsox404 b
where 2003 <= FY_IC_OP <= 2006;
quit;
/*22940 observations*/

Proc SQL;
create table GVKey as
select a.gvkey, b.*
from comp.funda a, mw_404 b
where a.cik=b.company_fkey
and 2004<=fyear<=2005;
quit;
/*50,867*/

Profile
 
 
Posted: 27 May 2014 11:12 AM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

It looks like you have observations from AA from multiple years, and that you then match only on CIK as opposed to CIK and fiscal year.

Matching on both CIK and fyear:

Proc SQL;
create table GVKey as
select a.gvkeyb.*
from comp.funda amw_404 b
where a
.cik=b.company_fkey
and a.fyear b.fy_ic_op;
quit

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: 27 May 2014 11:28 AM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  3
Joined  2014-05-21

Hi Joost,

Thanks for the quick reply.  I ran the code below

proc sql
 
create table MW_404 as
 
select distinct b.company_fkeyb.FY_IC_OPb.COUNT_WEAKb.FYE_IC_OPb.IC_OP_TYPEb.IS_NTH_RESTATEb.FILE_DATEb.FORM_FKEY
 from aa
.auditsox404 b
 where 2003 
<= FY_IC_OP <= 2006;
quit;

Proc SQL;
 
create table GVKey as
 
select a.gvkeyb.*
 
from comp.funda amw_404 b
 where a
.cik=b.company_fkey
 
and a.fyear b.fy_ic_op;
quit

Now my sample goes from 22940 observations to 42169, still with some identical observations.  Would you recommend editing the code further or just running a proc sort with nodups?

Profile
 
 
Posted: 27 May 2014 11:56 AM   [ Ignore ]   [ # 3 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

No problem.

There are ‘duplicate’ records in AA that you selected; the variable ‘IC_OP_TYPE’ is often ‘a’, as well as ‘m’ (2 records). If you were to drop this variable in the first query, the records from AA would be unique.

I didn’t realize earlier that you need to filter Compustat Funda on industry format, etc; see below.

proc sql
 
create table MW_404 as
 
select distinct b.company_fkeyb.FY_IC_OPb.COUNT_WEAKb.FYE_IC_OP/* b.IC_OP_TYPE, */ b.IS_NTH_RESTATEb.FILE_DATEb.FORM_FKEY
 from aa
.auditsox404 b
 where 2003 
<= FY_IC_OP <= 2006;
quit;

Proc SQL;
 
create table GVKey as
 
select distinct a.gvkeyb.*
 
from comp.funda amw_404 b
 where a
.cik=b.company_fkey
 
and a.fyear b.fy_ic_op
 
and a.indfmt='INDL' and a.datafmt='STD' and a.popsrc='D' and a.consol='C' ;
quit

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