topic Re: Matching Based on closest value in SAS Data Management

Matching Based on closest value

Takdir — Thu, 17 Oct 2019 22:00:41 GMT

I have 2 tables that I need to match based o date and maturity. However the dates and maturity dont match exactly. So i need to take the closest value. Is there any simple way to do it ?

Date	maturity
01-Mar	1.2
01-Mar	2.3
01-Mar	3.1
02-Mar	1.4
02-Mar	1.6
02-Mar	2.7
03-Mar	1.1
03-Mar	2.3
03-Mar	3.4

Date_given	maturity_given	Rate
01-Mar	1	1
01-Mar	2	2
01-Mar	3	4
02-Mar	1	5
02-Mar	2	8
02-Mar	3	9
04-Mar	1	3
04-Mar	2	5
04-Mar	3	7

Thanks in Advance 😄

Re: Matching Based on closest value

ballardw — Thu, 17 Oct 2019 22:22:37 GMT

Do you have any restrictions such a "closest greater than" or "closest less than" ? Any rule for preference if you have two "closest" such as comparing 2 to 1.8 and 2.2 which both have an absolute difference of 0.2?

You really should show the desired result for your given example data as well. I am not quite sure if you are expecting to match on the date exactly or not as a condition.

Re: Matching Based on closest value

Takdir — Thu, 17 Oct 2019 22:32:18 GMT

For now i just need the closest. greater than or less than doesnt matter. but in future for some other matching i may need it. so some sample codes with that may also help for future work.

Re: Matching Based on closest value

ballardw — Thu, 17 Oct 2019 23:07:19 GMT

I think this does what you are asking:

Please note the data steps to provide example data. You will usually get better results providing data in this form pasted into code boxes opened using the forum's {I} icon.

data work.table1;
input Date $ maturity ;
datalines;
01-Mar 1.2 
01-Mar 2.3 
01-Mar 3.1 
02-Mar 1.4 
02-Mar 1.6 
02-Mar 2.7 
03-Mar 1.1 
03-Mar 2.3 
03-Mar 3.4 
;

data work.table2;
input Date_given $ maturity_given Rate ;
datalines;
01-Mar 1 1 
01-Mar 2 2 
01-Mar 3 4 
02-Mar 1 5 
02-Mar 2 8 
02-Mar 3 9 
04-Mar 1 3 
04-Mar 2 5 
04-Mar 3 7 
;

proc sql;
   create table work.want as
   select a.date, a.maturity, b.rate, abs(a.maturity-b.maturity_given) as diff
   from work.table1 as a
        join
        work.table2 as b
        on a.date=b.date_given
   group by a.date, a.maturity

   having diff= min(diff)
   ;
quit;

proc sql;
   create table work.want as
   select a.date, a.maturity, b.rate
   from work.table1 as a
        join
        work.table2 as b
        on a.date=b.date_given
   group by a.date, a.maturity

   having abs(a.maturity-b.maturity_given)= min(abs(a.maturity-b.maturity_given))
   ;
quit;

You could use other than the ABS function to calculate difference using the order you need. If the sign is important and then add an additional condition to the Having clause to include positive or negative values of the difference.

The diff variable

Re: Matching Based on closest value

Takdir — Thu, 17 Oct 2019 23:25:25 GMT

Date	maturity	Rate
01-Mar	1.2	1
01-Mar	2.3	2
01-Mar	3.1	4
02-Mar	1.4	5
02-Mar	1.6	8
02-Mar	2.7	9
03-Mar	1.1	3
03-Mar	2.3	5
03-Mar	3.4	7

This is the result I was expecting. The date doesnt match in the 2 tables because 1 has march 3 and the other has march 4

Re: Matching Based on closest value

ballardw — Fri, 18 Oct 2019 18:03:04 GMT

That might be an entirely different problem than you described. Is the actual issue that you have two tables that have the same number of rows (critical here) and just need to match them in order?

data work.table1;
input Date $ maturity ;
datalines;
01-Mar 1.2 
01-Mar 2.3 
01-Mar 3.1 
02-Mar 1.4 
02-Mar 1.6 
02-Mar 2.7 
03-Mar 1.1 
03-Mar 2.3 
03-Mar 3.4 
;

data work.table2;
input Date_given $ maturity_given Rate ;
datalines;
01-Mar 1 1 
01-Mar 2 2 
01-Mar 3 4 
02-Mar 1 5 
02-Mar 2 8 
02-Mar 3 9 
04-Mar 1 3 
04-Mar 2 5 
04-Mar 3 7 
;

data work.want;
   merge work.table1 work.table2;
   drop date_given maturity_given;
run;

If not then you I still think your problem description in incomplete.