topic Merging data with dates in SAS Programming

Merging data with dates

amanjot_42 — Thu, 18 Jun 2020 22:50:44 GMT

Hello All,

I am trying to merge two files on the basis of similar firm identifiers in the two files. However, the years are different in both the files. In the first file, loan transaction dates are mentioned, and in the second file data dates are given (last date of the fiscal year). So, I would like to merge two files on the basis of firm identifiers and these two dates. Loan date should match the data date (fiscal year) in the second file.

Cusp. Loan date. Loan amount
10222. 12/06/2011. 300000
10222. 17/01/2012. 700000
10222. 18/03/2013. 500000

10333. 05/09/2011 60000

10333. 08/10/2013 560000

10333. 18/10/2015 40000

Cusp. Data date. Fiscal year.
10222. 31/03/2011. 2010
10222. 31/03/2012. 2011
10222. 31/03/2013. 2012
10222. 31/03/2014. 2013

10333 31/01/2012 2011

10333 31/01/2013 2012

10333 31/01/2014 2013

10333 31/01/2015 2014

So, the first two loan transaction amounts (for 10222) should match the second file in the same fiscal year, i.e. 2011, considering the data date. Similarly, for 10333, first loan transaction should match with the fiscal year 2011 in the second file.

Regards,

Re: Merging data with dates

Reeza — Thu, 18 Jun 2020 22:32:07 GMT

Your fiscal year starts in April it seems?

You can use INTNX() to align a date to the beginning or end of the year and you can play around with modifying the first parameter to get the right interval to align with your fiscal year. Otherwise a basic if/then statement also works.

Use something like this to first convert the loan_date to a 'date' and then merge on the date.

if month(loan_date) <=3 then fiscal_year = year(loan_date)-1; else fiscal_year = year(loan_date);

date = mdy(3, 31, fiscal_year+1);

Now merge on the new calculated date in your loan table.

@amanjot_42 wrote:
Hello All,

I am trying to merge two files on the basis of similar firm identifiers in the two files. However, the years are different in both the files. In the first file, loan transaction dates are mentioned, and in the second file data dates are given (last date of the fiscal year). So, I would like to merge two files on the basis of firm identifiers and these two dates. Loan date should match the data date (fiscal year) in the second file.

Cusp. Loan date. Loan amount
10222. 12/06/2011. 300000
10222. 17/01/2012. 700000
10222. 18/03/2013. 500000

Cusp. Data date. Fiscal year.
10222. 31/03/2011. 2010
10222. 31/03/2012. 2011
10222. 31/03/2013. 2012
10222. 31/03/2014. 2013

So, the first two loan transaction amounts should match the second file in the same fiscal year, i.e. 2011, considering the data date.

Regards,

Re: Merging data with dates

amanjot_42 — Thu, 18 Jun 2020 22:35:23 GMT

Thanks for your reply,

One thing, I forgot to mention is that there are different firms in the sample, and not all of them are having the same data dates (I mean, 31st march as the last date of the fiscal year). Some have 30th June, some have 30th September, and some even have 31st Jan!

Regards,

Aman

Re: Merging data with dates

Reeza — Thu, 18 Jun 2020 22:43:13 GMT

You definitely did not mention that and your example does not support that. Please expand your example to incorporate your additional requirements. Preferably as a data step.

Re: Merging data with dates

amanjot_42 — Thu, 18 Jun 2020 22:51:56 GMT

Sorry about that!

Please have a look at the example now!

Regards,

Re: Merging data with dates

smantha — Thu, 18 Jun 2020 22:54:37 GMT

1. Build a format based on your fiscal date begin and end i.e. begin and end marking a fiscal period

1Apr2011 - 31Mar2012 = 31Mar2012

2. Apply your format to your first dataset.

3. after doing that join it with the second dataset.

4. If there are overlapping ranges then it will be a problem

Idea 2: Work backwards

1. On dataset build the start of fiscal year for each row.

2. do a sql join based on your id and date from dataset 1 falls between start and end dates of fiscal year in dataset 2

Re: Merging data with dates

smantha — Thu, 18 Jun 2020 23:08:48 GMT

data table_1;
informat loan_date ddmmyy10.;
format loan_date ddmmyy10.;
input cusp loan_date loan_amount;
cards;
10222 12/06/2011 300000
10222 17/01/2012 700000
10222 18/03/2013 500000
10333 05/09/2011 60000
10333 08/10/2013 560000
10333 18/10/2015 40000
;;;
data table2;
informat fy_end_date ddmmyy10.;
format fy_end_date fy_beg_date ddmmyy10.;
input cusp fy_end_date Fyear;
fy_beg_date = intnx('year',fy_end_date,-1,'same');
cards;
10222 31/03/2011 2010
10222 31/03/2012 2011
10222 31/03/2013 2012
10222 31/03/2014 2013
10333 31/01/2012 2011
10333 31/01/2013 2012
10333 31/01/2014 2013
10333 31/01/2015 2014
;;;
run;

proc sql;
create table table3 as
select a.*,b.Fyear,b.fy_end_date from
table_1 a,
table2 b
where a.cusp = b.cusp
and a.loan_date > b.fy_beg_date 
and a.loan_date <= b.fy_end_date
;
quit;

Re: Merging data with dates

amanjot_42 — Mon, 22 Jun 2020 18:09:41 GMT

Thank you so much!

It worked perfectly!