Ok, the first dataset looks like this. The name of the file is MA2. I use the Acquiror_6digit_CUSIP and Date_Announced columns to merge it with the second dataset MA3 which I am also sharing. MA2: MA3:   MA2 has 3000 observations, MA3 has 100000 observations. I am trying to merge these two with the following criteria: MA2.Acquiror_6digit_CUSIP is equal to MA3.NCUSIP and MA2.Date_Announced is between MA3.NAMEDT and MA3.NAMEENDT.    Currently, I am using this code: proc sql; create table MA4 as select MA2.*, MA3.* from MA2 inner join Zeynep.Dsenames as MA3 on MA2.Date_Announced between MA3.NAMEDT and MA3.NAMEENDT and strip(MA2.Acquiror_6digit_CUSIP) = upcase(substr(strip(MA3.NCUSIP),1,6)) ; quit;   The result of this looks exactly like I want where I can see all the columns in both datasets if they are matched with my criteria. However, this result contains 2400 observations while MA2 had 3000. So I basically want to be able to see 3000 observations in the final output. 2400 of them should be matched with MA3 and 600 should display missing values. I hope I explained it. Thank you.   ... View more

yes there are many to many for one criteria (cusip), the second criteria (date) ensures that there is only one match for the first criteria. there are no missing variables. I can't use full join, I want the final output to have as many observations as in the first dataset. in the second dataset there are many irrelevant observations. ... View more

thank you for your response, the code works except that have1 has 3000 observations, have2 has 100000 observations. I want to have the final output to have 3000 observations, some items matched with have2 some unmatched. using this code, i have an output with around 100000 observations. ... View more

this solution increased the number of matched items. however, my main data has 3000 items, after applying the code you shared, 2300 items are merged using the code below:   proc sql; create table MA4 as select MA2.*, MA3.* from MA2 inner join Zeynep.Dsenames as MA3 on MA2.Date_Announced between MA3.NAMEDT and MA3.NAMEENDT and strip(MA2.Acquiror_6digit_CUSIP) = upcase(substr(strip(MA3.NCUSIP),1,6)) ; quit;   To see which items are not matched I use the code below:   proc sql; create table Unmatched_MA3 as select MA2.Acquiror_6digit_CUSIP, MA2.Date_Announced, MA2.Acquiror_Full_Name from MA2 left join MA3 on MA2.Acquiror_6digit_CUSIP = MA3.NCUSIP where MA3.NCUSIP is missing; quit;   this results in 800 observations. I would expect that the number of observations in matched and unmatched datasets would sum up to 3000.    Instead of getting a merged and unmatched output, can I change the below code some way that unmatched items are displayed as missing:     proc sql; create table MA4 as select MA2.*, MA3.* from MA2 inner join Zeynep.Dsenames as MA3 on MA2.Date_Announced between MA3.NAMEDT and MA3.NAMEENDT and strip(MA2.Acquiror_6digit_CUSIP) = upcase(substr(strip(MA3.NCUSIP),1,6)) ; quit;   Thank you ... View more

Hello, Thank you very much for your time and responses. Below is an example of unmatched data in MA2: Acquiror_6digit_CUSIP Date_Announced Acquiror_Full_Name 09253U 10/02/2017 Blackstone Group LP 09253U 20/03/2017 Blackstone Group LP 09253U 27/11/2017 Blackstone Group LP G0408V 03/05/2017 Aon PLC G0408V 01/09/2017 Aon PLC   Here are the corresponding lines in the other file (MA3) that should have matched: PERMNO NAMEDT NAMEENDT NCUSIP COMNAM 61735 02/04/2012 07/01/2014 G0408V10 AON PLC 61735 08/01/2014 23/11/2014 G0408V10 AON PLC 61735 24/11/2014 31/03/2020 G0408V10 AON PLC 92108 22/06/2007 30/06/2019 09253U10 BLACKSTONE GROUP LP ... View more

They are both characters. ... View more

Acquiror_6digit_CUSIP Date_Announced Acquiror_Full_Name Acquiror_8digit_CUSIP PERMNO NAMEDT NAMEENDT 466032 3.01.2017 J&J SNACK FOODS CORP 46603210 10026 10.06.2004 4.08.2019 466032 17.08.2017 J&J SNACK FOODS CORP 46603210 10026 10.06.2004 4.08.2019 68389X 19.01.2017 ORACLE CORP 68389X10 10104 15.07.2013 30.12.2022 68389X 18.12.2017 ORACLE CORP 68389X10 10104 15.07.2013 30.12.2022   This is an example of matched data, first column is from one table and the last two columns are from another table.  I match them using CUSIP. In the first table, I set the format of CUSIP as 6.. In the second table I cut the 8digit CUSIP to 6 digits and make sure the formats of dates also match between two tables.  data MA3; set Zeynep.Dsenames; NCUSIP=substr(CUSIP,1,6); format NAMEDT DATE9. NAMEENDT DATE9. NCUSIP 6.; run;   To merge two tables I use the following code: proc sql; create table MA4 as select MA2.*, MA3.* from MA2 inner join MA3 on MA2.Date_Announced between MA3.NAMEDT and MA3.NAMEENDT and MA2.Acquiror_6digit_CUSIP = upcase(MA3.NCUSIP) ; quit;   This process results in 2200 matched and 800 unmatched items. I get an output of the unmatched items and manually see that they should also match the second table (I also got excel outputs and matched them using vlookup function) based on CUSIP and dates. CUSIP are 6 digit character for both datasets and dates are in Num DATE9. format in both datasets. I check these using proc contents. I can confirm that there are no spaces as if there were they wouldn't match in excel as well. ... View more