Date Match to closer

[Smitha9]

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.

[ballardw]

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;