Comparing dates with Standard dates

[newsas007]

Hi there, 

I have several dates that i would like to compare with a standard date and create a variable that is closest to standard date. The closest date can be before or after the standard date.

Date Have:

ID	Standard_Date	Date1	Date2	Date3	Date4
1	1/1/2021	12/30/2020	1/25/2021	3/20/2020	9/20/2020
2	1/2/2021	12/2/2020	1/8/2021	3/21/2020	9/21/2020
3	1/3/2021	12/3/2020	1/27/2021	12/28/2020	9/22/2020
4	1/4/2021	12/4/2020	1/18/2021	3/23/2020	9/23/2020

Data Want:

ID	Standard _Date	Closest_date	Date_diff
1	1/1/2021	12/30/2020	2
2	1/2/2021	1/8/2021	-6
3	1/3/2021	12/28/2020	6
4	1/4/2021	1/18/2021	-14

[PeterClemmensen · Accepted solution]

Try this

data have;
input ID (Standard_Date Date1 - Date4)(:mmddyy10.);
format Standard_Date Date1 - Date4 mmddyy10.;
datalines;
1 01/01/2021 12/30/2020 01/25/2021 03/20/2020 09/20/2020
2 01/02/2021 12/02/2020 01/08/2021 03/21/2020 09/21/2020
3 01/03/2021 12/03/2020 01/27/2021 12/28/2020 09/22/2020
4 01/04/2021 12/04/2020 01/18/2021 03/23/2020 09/23/2020
;

data want;
   set have;
   array d Date1 - Date4;
   c = constant('big');
   do over d;
      diff = abs(d - Standard_Date);
      if diff < c then do;
         Date_diff = d - Standard_Date;
         closest_date = d;
         c = diff;
      end;
   end;
   format closest_date mmddyy10.;
   keep ID Standard_Date closest_date Date_diff;
run;

Result:

ID  Standard_Date  Date_diff  closest_date 
1   01/01/2021     -2         12/30/2020 
2   01/02/2021      6         01/08/2021 
3   01/03/2021     -6         12/28/2020 
4   01/04/2021      14        01/18/2021 

[newsas007]

I have missing values in the data and it is not working on my dataset. i am comparing around 6 dates to the standard date.  

I am getting missing values in closest date. Also checked column c has missing dates as well
I just need the closest_date and the date_diff can be calculated afterwards.

[novinosrin]

Hi @newsas007  Keeping it simple with a rather boring solution of course-

data have;
input ID (Standard_Date Date1 - Date4)(:mmddyy10.);
format Standard_Date Date1 - Date4 mmddyy10.;
datalines;
1 01/01/2021 12/30/2020 01/25/2021 03/20/2020 09/20/2020
2 01/02/2021 12/02/2020 01/08/2021 03/21/2020 09/21/2020
3 01/03/2021 12/03/2020 01/27/2021 12/28/2020 09/22/2020
4 01/04/2021 12/04/2020 01/18/2021 03/23/2020 09/23/2020
;

proc transpose data=have out=_have(drop=_name_);
 by id Standard_Date;
 var date1-date4;
run;

proc sql;
 create table want as
 select *, abs(Standard_Date-col1) as date_diff
 from _have
 group by id
 having min(date_diff)=date_diff;
quit;

ID	Standard_Date	COL1	date_diff
1	01/01/2021	12/30/2020	2
2	01/02/2021	01/08/2021	6
3	01/03/2021	12/28/2020	6
4	01/04/2021	01/18/2021	14

[newsas007]

@novinosrin, Thank you for this. Here is the dataset and the code. There is some problem with a few observations.
For example:
for ID#2 the closest date for standard date 08/08/2016 is Date5 08/10/2016.
Similary for ID#4 the closest date for standard date 10/12/2016 is Date6 06/08/2016
Closest date can be before or after Standard_date. Hope this helps

data have;
input ID (Standard_Date Date1 - Date6)(:mmddyy10.);
format Standard_Date Date1 - Date6 mmddyy10.;
datalines;
1 8/17/2016 . . 3/20/2016 5/19/2016 12/26/2016 2/2/2017
2 8/8/2016 . . 4/14/2016 7/3/2016 8/10/2016 .
3 8/31/2016 . . 5/14/2014 2/16/2016 . .
4 10/12/2016 . . 5/12/2013 2/20/2014 8/14/2015 6/8/2016
5 11/2/2016 . . 6/6/2013 9/26/2013 4/9/2014 8/14/2015
6 10/24/2016 . . 7/15/2014 1/10/2017 1/7/2018 .
7 8/18/2016 . . 3/19/2015 11/29/2015 6/2/2016 1/31/2017
8 11/1/2016 . . 10/15/2006 8/14/2014 11/15/2014 1/14/2016
9 11/29/2016 6/9/2013 9/5/2013 12/30/2014 12/3/2015 5/30/2016 .
;

proc transpose data=have out=_have(drop=_name_);
by id Standard_Date;
var date1-date4;
run;

proc sql;
create table want as
select *, abs(Standard_Date-col1) as date_diff
from _have
group by id
having min(date_diff)=date_diff;
quit;

[r_behata]

data have;
input ID (Standard_Date Date1 - Date6)(:mmddyy10.);
format Standard_Date Date1 - Date6 mmddyy10.;
datalines;
1 8/17/2016 . . 3/20/2016 5/19/2016 12/26/2016 2/2/2017
2 8/8/2016 . . 4/14/2016 7/3/2016 8/10/2016 .
3 8/31/2016 . . 5/14/2014 2/16/2016 . .
4 10/12/2016 . . 5/12/2013 2/20/2014 8/14/2015 6/8/2016
5 11/2/2016 . . 6/6/2013 9/26/2013 4/9/2014 8/14/2015
6 10/24/2016 . . 7/15/2014 1/10/2017 1/7/2018 .
7 8/18/2016 . . 3/19/2015 11/29/2015 6/2/2016 1/31/2017
8 11/1/2016 . . 10/15/2006 8/14/2014 11/15/2014 1/14/2016
9 11/29/2016 6/9/2013 9/5/2013 12/30/2014 12/3/2015 5/30/2016 .
;
run;


data want;
	set have;
	array date[*]  Date:;
	array closest[999] _temporary_;
	

	do i=1 to dim(date);
		closest[i]=abs(Standard_Date-date[i]);
	end;

	Closest_date=  date[whichn(min(of closest[*]),of closest[*])];
	Date_diff = Standard_Date -Closest_date;
	format Closest_date mmddyy10.;

	drop i;
run;

               Standard_                                                                                          Closest_    Date_
 Obs    ID          Date         Date1         Date2         Date3         Date4         Date5         Date6          date     diff

  1      1    08/17/2016             .             .    03/20/2016    05/19/2016    12/26/2016    02/02/2017    05/19/2016       90
  2      2    08/08/2016             .             .    04/14/2016    07/03/2016    08/10/2016             .    08/10/2016       -2
  3      3    08/31/2016             .             .    05/14/2014    02/16/2016             .             .    02/16/2016      197
  4      4    10/12/2016             .             .    05/12/2013    02/20/2014    08/14/2015    06/08/2016    06/08/2016      126
  5      5    11/02/2016             .             .    06/06/2013    09/26/2013    04/09/2014    08/14/2015    08/14/2015      446
  6      6    10/24/2016             .             .    07/15/2014    01/10/2017    01/07/2018             .    01/10/2017      -78
  7      7    08/18/2016             .             .    03/19/2015    11/29/2015    06/02/2016    01/31/2017    06/02/2016       77
  8      8    11/01/2016             .             .    10/15/2006    08/14/2014    11/15/2014    01/14/2016    01/14/2016      292
  9      9    11/29/2016    06/09/2013    09/05/2013    12/30/2014    12/03/2015    05/30/2016             .    05/30/2016      183

[newsas007]

@r_behata : Perfect and works well. 

[ballardw]

---

@newsas007 wrote:

I have missing values in the data and it is not working on my dataset. i am comparing around 6 dates to the standard date.  

I am getting missing values in closest date. Also checked column c has missing dates as well
I just need the closest_date and the date_diff can be calculated afterwards.

---

Large economy sized hint: When you post example data then it needs to be representative of your data. If you have some missing values then you need to include some missing values. If you have records with all missing values except the standard date include one of those and what you expect a result to be.

We do not know your data. If you provide a simple example we will generally go for a simple solution. Without specific examples, or at least statements that some of the data is missing, the solutions are likely not to go there.

[newsas007]

@ballardw , Yes sir, i used the data that is most representative in my reply. Thank you

[r_behata]

data have;
	input ID (Standard_Date Date1 - Date4)(:mmddyy10.);
	format Standard_Date Date1 - Date4 mmddyy10.;
	datalines;
1 01/01/2021 12/30/2020 01/25/2021 03/20/2020 09/20/2020
2 01/02/2021 12/02/2020 01/08/2021 03/21/2020 09/21/2020
3 01/03/2021 12/03/2020 01/27/2021 12/28/2020 09/22/2020
4 01/04/2021 12/04/2020 01/18/2021 03/23/2020 09/23/2020
;
run;

data want;
	set have;
	array date[*]  Date1 - Date4;
	array closest[999] _temporary_;

	do i=1 to dim(date);
		closest[i]=abs(Standard_Date-date[i]);
	end;

	Closest_date=  date[whichn(min(of closest[*]),of closest[*])];
	Date_diff = Standard_Date -Closest_date;
	format Closest_date mmddyy10.;

	drop i;
run;

[newsas007]

@r_behata,

Thank you for this. But it needs a little tweaking. Pls see my reply above.