Date Match to closer

Smitha9 — Fri, 22 Jul 2022 15:54:08 GMT

Hi,

I have a dataset

ID Date Measure

1 01/01/2005 02/03/2007

1 02/03/2008 03/04/2010

1 03/20/2011 05/06/2009

I want the "Measure Date" closer to the "Date"

ID Date Measure

1 01/01/2005 02/03/2007

1 02/03/2008 02/03/2007

1 03/20/2011 03/04/2010

The measure date 02/03/2007 is closer to both 01/01/2005 and 02/03/2008 and measure date 03/04/2010 is closer to 03/20/2011

Can I do this is SAS?

thanks so much.

Re: Date Match to closer

ballardw — Fri, 22 Jul 2022 17:33:51 GMT

Rules are needed as you have a very limited example.

Is this to be restricted to within values of a single ID or not?

Is only the immediately previous record to be compared or all values of the ID?

Does the closer have to be prior to the date? (your examples are but not stated if that is a requirement)

Something that duplicates your want that may work assuming you want to search within ALL the values of ID and that your dates are actually SAS date values (numeric with a format like MMDDYY10).

Note the data provided as a data step. (Hint Hint Hint)

data have;
   input ID  Date :mmddyy10.  Measure :mmddyy10.;
   format date measure mmddyy10.;
datalines;
1      01/01/2005        02/03/2007
1      02/03/2008        03/04/2010
1      03/20/2011         05/06/2009
;

proc sql;
   create table want as
   select a.id,a.date,a.measure,b.measure as closermeasure

   from have as a
        left join
        have as b
        on a.id=b.id
   group by a.id, a.date
   having abs(a.date-b.measure)= min(abs(a.date-b.measure))
   ;
quit;

topic Date Match to closer in SAS Programming

Date Match to closer

Re: Date Match to closer