I have a problem regarding left join while merging data. I tried to add tickers (from file b) into file a. I find that when both date and cusip are matched and the tickers exist in the file b, some tickers are not added. Is this because different formats of the date data? Can anybody tell me how to solve the problem?
Below is the code I used:
PROC SQL;
create table fsample.fi_director2007_2013_tick (keep = mgdate tick cusip)as
select distinct a.*,b.tick
from fsample.fi_director2007_2013 a
left join fsample.director2007_2013 b
on a.mgdate = b.meetingdate and a.cusip = b.cusip;
run;
————————————————————————————
note: mgdate (format ddmmyy10. ; informat DATE9.);
meetingdate (format ddmmyy10. ; informat 10.);
————————————————————————————
some examples:
000360206 19/05/2009
000361105 14/10/2009
000886309 04/03/2009
000957100 03/03/2009
are valid in file a and file b, but the ticks are not added.
file after merging
000000000 09/05/2012 WRC
000000000 31/05/2012 WBSN
000360206 19/05/2009
000360206 25/05/2010 AAON
000360206 17/05/2011 AAON
000360206 15/05/2012 AAON
000360206 21/05/2013 AAON
000361105 17/10/2007 AIR
000361105 08/10/2008 AIR
000361105 14/10/2009
000361105 13/10/2010 AIR
000361105 12/10/2011 AIR
000361105 10/10/2012 AIR
000361105 09/10/2013 AIR
000886200 06/03/2007 ADCT
000886309 06/03/2008 ADCT
000886309 04/03/2009
000957100 06/03/2007 ABM
000957100 04/03/2008 ABM
000957100 03/03/2009
000957100 02/03/2010 ABM
000957100 08/03/2011 ABM
————————————————————
file a
000000000 09/05/2012
000000000 31/05/2012
000360206 19/05/2009
000360206 25/05/2010
000360206 17/05/2011
000360206 15/05/2012
000360206 21/05/2013
000361105 17/10/2007
000361105 08/10/2008
000361105 14/10/2009
000361105 13/10/2010
000361105 12/10/2011
000361105 10/10/2012
000361105 09/10/2013
000886200 06/03/2007
000886309 06/03/2008
000886309 04/03/2009
000957100 06/03/2007
000957100 04/03/2008
000957100 03/03/2009
000957100 02/03/2010
000957100 08/03/2011
000957100 06/03/2012
000957100 05/03/2013
—————————————————————
file b
000000000 09/05/2012 WRC
000000000 31/05/2012 WBSN
000360206 19/05/2009 AAON
000360206 25/05/2010 AAON
000360206 17/05/2011 AAON
000360206 15/05/2012 AAON
000360206 21/05/2013 AAON
000361105 17/10/2007 AIR
000361105 08/10/2008 AIR
000361105 14/10/2009 AIR
000361105 13/10/2010 AIR
000361105 12/10/2011 AIR
000361105 10/10/2012 AIR
000361105 09/10/2013 AIR
000886200 06/03/2007 ADCT
000886309 06/03/2008 ADCT
000886309 04/03/2009 ADCT
000957100 06/03/2007 ABM
000957100 04/03/2008 ABM
000957100 03/03/2009 ABM
000957100 02/03/2010 ABM
000957100 08/03/2011 ABM
000957100 06/03/2012 ABM
000957100 05/03/2013 ABM