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

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

Sandy — Mon, 20 May 2013 03:38:17 GMT

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

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

Tom — Mon, 20 May 2013 05:09:21 GMT

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;

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

Sandy — Mon, 20 May 2013 12:33:37 GMT

Thanks Tom