Dear all, I am cleaning the data and after merge I have found that I have two different values of the same variable over the same time period. It happened since the company switched its fiscal years. HOw can I get around this problem? X_variable is reported from Compustat based on Fiscal Periods. DATADATE = end of fiscal period. FYR = month(DATADATE). BEGFYR = beginning of fiscal period. As_of_Date is taken from another dataset and it seems that it falls into two different fiscal periods because this company (ID = 123456) changed its reporting date from December (FYR = 12) until september (FYR=09) and then used FYR = 09 until today. So this swtich happened once. What should I do in this case? If the company switched from 12 (up until 2009) to 9(from 2010), how can I say that for the For 2009 we have a long year (from 2009-01-01 until 2009-12-31) So if As_of_Date falls between these two dates, we report X_variable = 472 For 2010 we will have a short year (from 2010-01-01 until 2010-09-30). So As_of_Date won't fall between these two dates, so we will omit the duplicate values. So that the fiscal year periods will look like: 2009-01-01 until 2009-12-31 (12 months) 2010-01-01 until 2010-09-30 (10 months) 2010-10-01 until 2011-09-30 (12 months) I think I need to change smth in the code in the definition of fiscal years: data sql.NEW; set sql. OLD; format begfyr endfyr yymmdd10.; fyr = month(DATADATE); endfyr = DATADATE; begfyr = intnx('month', endfyr, -11, 'beg'); run; proc sql; create table sql.merge as select * from sql. loan as a, sql.compustat as b where a.ID = b.ID and a.As_of_Date >= b.begfyr and a.As_of_Date <= b.endfyr ; quit; Original DATA ID As_of_Date DATADATE FYR BEGFYR X_variable 123456 2009-10-30 2009-12-31 12 2009-01-01 472 123456 2009-11-30 2009-12-31 12 2009-01-01 472 123456 2009-12-31 2009-12-31 12 2009-01-01 472 123456 2009-10-30 2010-09-30 09 2009-10-01 434 123456 2009-11-30 2010-09-30 09 2009-10-01 434 123456 2009-12-31 2010-09-30 09 2009-10-01 434
... View more