BookmarkSubscribeRSS Feed
Takdir
Obsidian | Level 7

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 ?

Datematurity
01-Mar1.2
01-Mar2.3
01-Mar3.1
02-Mar1.4
02-Mar1.6
02-Mar2.7
03-Mar1.1
03-Mar2.3
03-Mar3.4

 

 

Date_givenmaturity_givenRate
01-Mar11
01-Mar22
01-Mar34
02-Mar15
02-Mar28
02-Mar39
04-Mar13
04-Mar25
04-Mar37

 

Thanks in Advance 😄

5 REPLIES 5
ballardw
Super User

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.

Takdir
Obsidian | Level 7

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.

ballardw
Super User

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

Takdir
Obsidian | Level 7
DatematurityRate
01-Mar1.21
01-Mar2.32
01-Mar3.14
02-Mar1.45
02-Mar1.68
02-Mar2.79
03-Mar1.13
03-Mar2.35
03-Mar3.47

 

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

ballardw
Super User

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1389 views
  • 2 likes
  • 2 in conversation