BookmarkSubscribeRSS Feed
Sandy
Calcite | Level 5

I have a variable delivery_date with one observation and test_date with 20 observations. I'd like to find the closest date in test_date to delivery_date by find the minimum absolute difference and retain the minimum value.

When I merge the datasets I only get two observations paired up and get missing values for the rest. Would anyone please explain how to go about this with maybe a do loop or...???

Thank you!

delivery_date
11/16/2011
Test_date
13-Mar-11
10-Apr-11
20-May-11
9-Jun-11
31-Jul-11
17-Aug-11
12-Sep-11
10-Nov-11
11-Oct-11
12-Dec-11
29-Feb-12
13-Mar-13
10-Apr-10
20-May-10
9-Jun-10
21-Jul-11
15-Aug-11
19-Oct-11
21-Nov-11
22-Dec-11
2 REPLIES 2
Tom
Super User Tom
Super User

You cannot merge by date as the dates do not match.

You could create a dummy variable that is a constant in each and merge on that (not really needed, but it you had a grouping variable it might help).

You could just set the single obs dataset once.

data want;

  if _n_=1 then set delivery_dataset;

  set test_dataset;

  diff = delivery_date - test_date;

  absdiff = abs(diff);

run;

Probably easiest to just use PROC SQL.

proc sql noprint ;

  create table want as

    select a.delivery_date,b.test_date,a.delivery_date-b.test_date as diff, abs(a.delivery_date - b.test_date) as absdiff

    from delivery_dataset a , test_dataset b

    order by 4

  ;

quit;

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
  • 2 replies
  • 2148 views
  • 0 likes
  • 2 in conversation