Dear All, I have used the following code to merge two data sets, proc sql;
create table own.want
as select a.*, b.*
from own.sub_9110 as a left join own.dir_own as b
on a.cusip=b.cusip
and fy between intnx('year', yearfiled, -2, "sameday") and yearfiled;
quit; I want to merge these two datasets by cusip (cusips are already in the same format) and pick up values from own.dir_own data( datasetb) which its fy(year variable in b) is exactly 2 years prior to the yearfield (year variable in a ). in a, I have thecompany list with cusip and yearfield(like 2004, 2005,...not in month and day format) and in b I have all the director ownership for each company from 1992 till 2018, cusip and fy. So, this means that in b dataset I have more than one director ownership for each company; Lets say company A in the data set a looks like: name yearfield cusip ..... A 2004 12345678 company A in the data set b looks like: name fy cusip dir_own .... A 1992 12345678 45 A 1993 12345678 12 A 2002 12345678 20 A 2004 12345678 44 the final merged data set should look like this : name cusip yearfiled fy dir_own A 12345678 2004 2002 20 so it picked the director ownership which is 2 years prior to the yearfield; but the code that i used grabbed all the observation, the final merged data looked like this : name cusip yearfiled fy dir_own A 12345678 2004 1992 45 A 12345678 2004 1993 12 A 12345678 2004 2002 20 A 12345678 2004 2004 44 I am not sure how to correct the code, hope you guys can help, thanks in advance. Ziba
... View more