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

hi,

That is weird; it seems as if the date in file b is not read correctly.

Can you try:

That should return the record in file b for ABM on that date. If it doesn’t give results, there is something going on with your dates (if so, is it stored as text as opposed to a number formatted as a date?).

best regards,

Joost

Thanks Joost.

I have tried the code you provided on both dataset, nothing returned. I only did modifications on file a. I remember file a was imported from an excel file, and dates were imported as char at the begin. I then forced the dates to numeric using INPUT function.  File b is the untouched director data from RiskMetrics. I check the type of the dates in file a and b, both are numeric. Is there anyway to solve this problem?

Many thanks,
L

Hi Joost,

I exported file a and file b to excel and re-modified them myself. Now they are working fine, but may I still ask that if I donot want to export the data and re-modify them, is there any code can do it?

Cheers.
L

hi L,

Hard to tell from here what’s happened. The curious thing is that it matched some records correctly. If something was wrongly imported, none of the records would match.

I noticed by the way that the code in my previous post should have been using ‘tick’ and ‘meetingdate’ (not ‘ticker’ and ‘date’).

best regards,

Joost

Hi Joost,

Thanks for your patient. tick is the ticker symbols after I have done some cleans. No problem with that.

Cheers.
L

hi,

I’m curious what is causing this trouble. Can you send the two (small) datasets a and b (in SAS format) to joost at ufl dot edu?

cheers,

Joost