BookmarkSubscribeRSS Feed
statadm
Fluorite | Level 6
I have two datasets that need merged together and even though they have an ID number, I need some other variable that will match them since they can have multiple records. My only option is to use a date variable. The catch is that the date variable will not be the same exact date, but should fall within a range of +/- 30 days.

I have the following merge variables in each dataset:

id examdate;

id examdate examdate_p30 examdate_m30;

examdate matches in 70% of the population, but I need to match the other 30% allowing examdate in dataset 1 to fall between examdate_p30 and examdate_m30 from dataset 2.

Any thoughts?

Thanks in advance.
5 REPLIES 5
Doc_Duke
Rhodochrosite | Level 12
This problem is pretty straightforward in PROC SQL (and a real mess with a data step).

Look at the BETWEEN operator for comparison in the WHERE clause. You may need to do two steps, one for the exact matches and one for the inexact ones.
statadm
Fluorite | Level 6
Ok, that sounds like a great idea. I was looking into something called fuzzy merges and couldn't get it working exactly right, so I will give this a try.

Thanks!
Haikuo
Onyx | Level 15

Hi Doc,

quote:"and a real mess with a data step"

Well, not if hash() can be qualified as data step implementation.

Kindly Regards,

Haikuo

tish
Calcite | Level 5

I'd do something like this. If there is no chance of multiple matches, stick with new_dataset_ver1 and order it however you want.

If there is a chance that there might be more than one match per ID, look at the code used to obtain new_dataset_ver2. I calculate a variable that rates the closeness of the match. Here I take the distance in days as an absolute value, then select the match with the closest value. I use the monotonic() function which can be tricky if you do more than use it to add row numbers. Here it is an example of how to rank potential matches.

This is untested code, but I do use the technique with time stamped dates. Be careful to set up decision rules (in the order by clause) carefully. The code sample below could force an arbitrary selection between two matches with the same time distance.

proc sql;

   create table new_dataset_ver1 as

      select

         a.ID,

         a.examdate as examdate_a,

         b.examdate as examdate_b,

         b.examdate_p30,

         b.examdate_m30,

         abs(a.examdate - b.examdate) as difference

      from

         dataset1 as a,

         dataset2 as b

      where

         a.ID = b.ID and

         b.examdate_p30 < a.examdate < b.examdate_m30

      order by

         ID,

         difference;

        

   create table monotonic as

      select

         *,

         monotonic() as line_number

      from

         new_dataset_ver1;

  

   create table min_linenumber as

      select

         ID,

         min(line_number) as closest_date

      from

        monotonic
  group by
        ID;

  

   create table new_dataset_ver2 as

      select

         a.*

      from

         monotonic as a,

         min_linenumber as b

      where

         a.ID = b.ID and

         a.line_number = b.closest_date;        

quit;

PGStats
Opal | Level 21

You can try something like this (untested), it tries to deal properly with missing matches in dataset b :

proc sql;
create table temp as
select a.*, b.examdate as otherDate, b.var1, b.var2,
     abs(intck("DAY", a.examdate, b.examdate)) as sepDays
from a left join b on a.id = b.id
where calculated sepDays <= 30
order by a.id, a.examdate, sepDays;
quit;

data want(drop=sepDays);
set temp;
by id examdate;
if first.examdate;
run;

PG

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 9018 views
  • 0 likes
  • 5 in conversation