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 | ||||||||||||||||||||||
|
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;
Thanks Tom
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.