Best method to retain records before and after a certain date
Posted: 18 August 2015 02:30 PM   [ Ignore ]
Newbie
Rank
Total Posts:  21
Joined  2014-07-18

Hello!

What is the best method to retain records before a certain event date.  I’d like to use three years before and after a certain date.  If the company does not have enough years to be included in my sample that I want to discard.

Thanks in advance!

samme

Profile
 
 
Posted: 18 August 2015 02:41 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Samme,

Asuming you have a dataset ‘events’ with evendate and gvkey, and want to match yearly data (for example dataset yearlyData, that also has gvkey and datadate), you could do something like:

proc sql;
  
create table dsOut as select a.*, b.atb.sale from dsin ayearlyData b where a.gvkey b.gvkey and abs(yrdif(evendatedatadate,'ACT/365')) <= 3;
quit;

/* or if yearlyData has a year variable */
proc sql;
  
create table dsOut as select a.*, b.atb.sale from dsin ayearlyData b where a.gvkey b.gvkey and b.year <= year(a.eventdate) <= b.year 3;
quit

abs(yrdif(evendate, datadate,‘ACT/365’)) <= 3 will keep records in funda where datadate is within 3 years from the event date.

SAS yrdif see: http://support.sas.com/documentation/cdl/en/lefunctionsref/63354/HTML/default/viewer.htm#p1pmmr2dtec32an1vbsqmm3abil5.htm

Hope this helps,

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: 18 August 2015 04:07 PM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  21
Joined  2014-07-18

I have one data set with all information.  There is an orderdate.  I want to keep companies with financial information for three years before and three years after this order date and discard all others.

I tried both of your suggestions.  Although I receive no errors, I still have companies with just two years of information.  Not sure why…

Thanks!

Profile
 
 
Posted: 18 August 2015 04:15 PM   [ Ignore ]   [ # 3 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

ok, I misunderstood—the code in my post would select 3 years around an event-date. I now understand that you want to keep events where you have at least 3 years of data around the event, and drop events where you don’t have such data.

How about this: made it a self join (all data is on the same dataset), and requiring at least 3 years of data before as well as after the event (the ‘having’ clauses should eliminate events with too few years around the event):

proc sql;
  
create table dsOut as select a.*, b.atb.sale from dsin adsin b where a.gvkey b.gvkey and yrdif(evendatedatadate,'ACT/365') as numyears group by eventdate having min(numyears) <= -and max(numyears) >= 3;
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: 18 August 2015 04:59 PM   [ Ignore ]   [ # 4 ]
Newbie
Rank
Total Posts:  21
Joined  2014-07-18

For some reason, it doesn’t like

yrdif(orderdate, datadate,‘ACT/365’) as

not sure why..

i’ve attached an image of the error.

Thanks for your help!!

Image Attachments
errormsg.PNG
Profile
 
 
Posted: 18 August 2015 05:15 PM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

ok, I was too quick, the creation of new variables needs to be in the select part (the yrdif part goes up a bit).

proc sql;
  
create table dsOut as select a.*, b.atb.sale,  yrdif(a.evendateb.datadate,'ACT/365') as numyears 
  from dsin a
dsin b 
  where a
.gvkey b.gvkey 
  group by eventdate 
  having min
(numyears) <= -and max(numyears) >= 3;
quit

In case you have multiple events for one or more firms, you need to make a new variable that combines the gvkey and the eventdate (data dsout; set dsin; key=gvkey || eventdate;run; and use “group by key” instead of “group by eventdate”).

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
 
 
Posted: 19 August 2015 12:34 PM   [ Ignore ]   [ # 6 ]
Newbie
Rank
Total Posts:  21
Joined  2014-07-18

Not sure why..  but this still will not work.  Everything is in one table.  there are multiple firms with the same event data.  I just want to keep those firms that have at least 3 years before and 3 years after of data from the event date.  this is the errors generated.

1150 proc sql;
1151   create table myreportdata1b as select a.*, yrdif(orderdate,
1151! datadate,‘ACT/365’) as numyears
1152   from myreportdata1a a, myreportdata1a b where a.gvkey = b.gvkey and
1152! a.fyear=b.fyear group
1153   by orderdate having min(numyears) <= -3 and max(numyears) >= 3;
ERROR: Ambiguous reference, column orderdate is in more than one table.
ERROR: Ambiguous reference, column datadate is in more than one table.
ERROR: Ambiguous reference, column orderdate is in more than one table.
1154 quit;
NOTE: The SAS System stopped processing this step because of errors.

Thanks again in advance!

Profile
 
 
Posted: 19 August 2015 12:41 PM   [ Ignore ]   [ # 7 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

Add ‘a’ or ‘b’ in front of the variables. Since it is a self-join, both variables appear on both datasets, so that needs to be specified. In my post yesterday (5:15 pm), I need to have “group by a.eventdate” instead of “group by eventdate”. I did have “a” and “b” on the yrdif function; you need to add that as well.

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
 
 
Posted: 19 August 2015 12:54 PM   [ Ignore ]   [ # 8 ]
Newbie
Rank
Total Posts:  21
Joined  2014-07-18

No error messages, but not removing those that don’t meet the criteria.

You can see in prob2, that agl resources is the only record (dataset is sortd by gvkey and fyear).  and then AULT, Inc has just two records.  In prob1, we have three records for CECO International…

Image Attachments
prob2.PNG
Profile
 
 
Posted: 19 August 2015 12:55 PM   [ Ignore ]   [ # 9 ]
Newbie
Rank
Total Posts:  21
Joined  2014-07-18

here is prob1

Image Attachments
Prob1.PNG
Profile
 
 
Posted: 19 August 2015 01:05 PM   [ Ignore ]   [ # 10 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

Can you run this?

data test (keep gvkey fyear datadate otherdate);
set comp.funda;
if 
fyear >= 2010;
otherdate datadate ;
run;

proc sort data=test nodupkeyby gvkey fyear;run;

proc sql;
  
create table dsOut as select a.*, b.fyear as fyear_b,  yrdif(a.datadateb.datadate,'ACT/365') as numyears 
  from test a
test b 
  where a
.gvkey b.gvkey 
  group by a
.gvkey
  having min
(numyears) <= -and max(numyears) >= 2;
quit

proc sort data=test by gvkey fyear;run

This seems to work (but does have ‘duplicates’ as the different years for ‘b’ are repeated in the output set).

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
 
 
Posted: 19 August 2015 01:21 PM   [ Ignore ]   [ # 11 ]
Newbie
Rank
Total Posts:  21
Joined  2014-07-18

have firms with just one record.  see gvkey 001013 and 001082

Image Attachments
Prob1.PNGprob2.PNG
Profile
 
 
Posted: 19 August 2015 01:33 PM   [ Ignore ]   [ # 12 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Samme,

It was a nice experiment, but it doesn’t seem to converge to something working. (I don’t have firms with a single record though.)

How about instead of doing something fancy in one step, just do it in 3 steps:
- create a dataset with events (keep: gvkey and eventdate), call it eventsample
- do an innerjoin with the eventsample and the dataset you have now that has the yearly stuff: join on gvkey and get data for fyear = year(eventdate) - 3; if that data is not there, it will be dropped from the output, exactly how you want it
- repeat for fyear = year(eventdate) + 3: you will be left with events that satisfy your data requirements

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
 
 
   
 
 
‹‹ statistical analysis      Merging compustat ››