DATA Step, Macro, Functions and more

How to find ABS value between two variables one with 1 obs and another with 20 obs?

Reply
Occasional Contributor
Posts: 7

How to find ABS value between two variables one with 1 obs and another with 20 obs?

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
Super User
Super User
Posts: 7,074

Re: How to find ABS value between two variables one with 1 obs and another with 20 obs?

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;

Occasional Contributor
Posts: 7

Re: How to find ABS value between two variables one with 1 obs and another with 20 obs?

Thanks Tom

Ask a Question
Discussion stats
  • 2 replies
  • 354 views
  • 0 likes
  • 2 in conversation