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.
... View more