Merging two tables by choosing the CLOSEST dates

[nazmul]

Dear ChrisNz,

Thank you for your help.

After I run the following code, I did not get consistent  result. I have included the picture of my output file at the bottom of the post. As you can see, firm 859 in year 2011 should not have any matched firm but my output shows matched firms.  also firm 859 in year 2000 should not have any cusip_matching9 and cusip_matching9.

I want to keep all the sampling firms including those for which there is no matching firms. Your last code keeps all the sampling firms but gives inconsistent results.

I have included the snapshot of the output file in this post.

data sample;
input fyear cusip SIC2 wBAHR1hat_rank;
cards;
1990 123 12 12
2000 859 11 15
2001 859 11 15
;
data control;
input fyear cusip SIC2 wBAHR1hat_rank;
cards;
1990 124 12 13
1990 125 12 14
1990 126 12 15
1990 127 12 16
1990 128 12 17
1990 129 12 18
1990 759 13 19
1990 760 15 20
1990 761 12 21
1990 762 12 22
1990 763 12 23
1990 764 12 24
2000 860 11 21
2000 861 11 12
2000 863 11 23
2000 864 11 13
2000 867 11 14
2000 868 11 16
2000 869 11 17
2000 870 12 18
2000 872 14 19
2000 873 11 20
2000 874 11 21
2000 876 11 22
2000 877 11 23
;
proc sql;
create table _V as
select a.*
,abs(a.WBAHR1HAT_RANK-b.WBAHR1HAT_RANK) as DIF
,b.CUSIP as CUSIPB
from SAMPLE a
left join
CONTROL b
on a.FYEAR = b.FYEAR
and a.SIC2 = b.SIC2
and b.WBAHR1HAT_RANK >= a.WBAHR1HAT_RANK
order by 1,2,3,4,5 ;
quit;
proc sort data=_V; by FYEAR CUSIP SIC2 WBAHR1HAT_RANK; run;

data test;
set _V;
retain N 0 CUSIP_MATCHING1-CUSIP_MATCHING12;
by FYEAR CUSIP SIC2 WBAHR1HAT_RANK;
N=N*^first.WBAHR1HAT_RANK+1;
if N= 1 then CUSIP_MATCHING1 = CUSIPB;
if N= 2 then CUSIP_MATCHING2 = CUSIPB;
if N= 3 then CUSIP_MATCHING3 = CUSIPB;
if N= 4 then CUSIP_MATCHING4 = CUSIPB;
if N= 5 then CUSIP_MATCHING5 = CUSIPB;
if N= 6 then CUSIP_MATCHING6 = CUSIPB;
if N= 7 then CUSIP_MATCHING7 = CUSIPB;
if N= 8 then CUSIP_MATCHING8 = CUSIPB;
if N= 9 then CUSIP_MATCHING9 = CUSIPB;
if N=10 then CUSIP_MATCHING10 = CUSIPB;
if N=11 then CUSIP_MATCHING11 = CUSIPB;
if N=12 then CUSIP_MATCHING12 = CUSIPB;
if N=12 or last.WBAHR1HAT_RANK then output;
run;

However, your first code gives me consistent results but it removes the sampling firms that have no matching firms.

[nazmul]

Dear ChrisNz,

I just successfully modified your code to retain all the sample companies. My problem is solved. Thank you for your help.