topic Re: How to merge datasets based on date of service within 7 days and two other variables? in SAS Enterprise Guide

How to merge datasets based on date of service within 7 days and two other variables?

csantia — Thu, 20 Jul 2023 21:21:49 GMT

I have two datasets and I need to merge them together. These datasets will require a many to many merge. One is a healthcare visit dataset and the other is a medication pick-up dataset. Two of the variables I need to merge on are a patient id and provider id and then I also need to merge on a visit date. However I do not need the visit date to be the same, I need it to be either on the same day or within the following X amount of days (7 for example). I am not sure how I can merge two datasets on an inexact but specified timeframe rather than a single variable. Is there a way to do this? Is there another approach for this outside of also merging on date?

Thank you!

Re: How to merge datasets based on date of service within 7 days and two other variables?

ballardw — Thu, 20 Jul 2023 22:22:11 GMT

Not much detail to go on, no data set names, no variable names.

You can select with something like this, assuming your date values are actually SAS date values:

 JOIN ON basedate le desireddate le intnx('days',basedate,7)

if you want a desired date variable to between some other date variable and 7 days later. SAS dates you could actually just use (basedate+7) but the intnx function would be the way to increment another date by week, month, quarter or year with some others mixed in.

This would be done in Proc SQL to get reliable many-to-many join.

Likely you would have aliases indicating which dataset each of your date variables comes from.

I think you likely would be using the provider visit date as "basedate" and the medication date as the "desireddate" above.

Re: How to merge datasets based on date of service within 7 days and two other variables?

mkeintz — Thu, 20 Jul 2023 23:00:49 GMT

Please provide sample data of what you HAVE and what you WANT. You'll get much better results.