topic Re: Calculate difference in days between two dates for all possible pairwise date combinations in SAS Programming

Calculate difference in days between two dates for all possible pairwise date combinations

Solsidan — Tue, 28 May 2019 14:36:11 GMT

Hi there,

I need your help in solving a problem using SAS. I am interested in calculating the difference in the number of days between all possible pairwise date combinations (from Date_1 to Date_7) for each id.

The eventual goal is to have a single variable indicator ("lessthan400days") per id that takes the value of 1 if the difference for ANY pairwise combination is less than 400 days, value = 0 if all possible pairwise combinations are greater, value = . if there is less than two dates for all date range (date_1 to date_7) e.g. variable lessthan400days for id = 1 would be lessthan400days = 1 because the date pairs (date_1 and date_5) has 107 days, or pairs (date_3 and date_4) has 308 days. For ids 4 and 5, variable lessthan400days = .

So here are some tips to work with:

No need to store the actual calculation results between the pair combinations.
If any pair combination is less than or equal to 400, the calculations can stop for that id.
If for an id there is only one date variable, or all dates are missing i.e. no pair e.g. ids 4 and 5, then set value to missing (".")

I have looked at posts that calculate date differences but none seems to do what I hope to achieve.

I shall greatly appreciate your help!

data have;
input id (Date_1-Date_7) (: yymmdd10.) ;
format Date_1-Date_7 yymmdd10.;
cards;
1     2005/07/28  2016/12/23  2011/03/29  2012/01/30  2005/11/11  2016/12/23  2014/04/10
2     2006/06/16  2005/07/13  2007/06/27  2007/06/19  2005/07/13  2010/10/29  2015/09/29
3     2011/08/19  2010/04/12  2010/04/12  2012/08/21  2007/01/12  2015/12/18  2011/09/09
4     .  .  .  .  .  .  .
5     .  2016/01/21  .  .  .  .  .
;
run;

Re: Calculate difference in days between two dates for all possible pairwise date combinations

PaigeMiller — Tue, 28 May 2019 14:57:03 GMT

data have;
input id (Date_1-Date_7) (: yymmdd10.) ;
array d date_1-date_7;
flag=.;
do i=1 to dim(d)-1;
	do j=(i+1) to dim(d);
	    if not missing(d(j)) and not missing(d(i)) and abs(d(j)-d(i)) < 400 then do;
                flag=1; 
                leave;
            end;
	end;
end;
format Date_1-Date_7 yymmdd10.;
drop i j;
cards;
1     2005/07/28  2016/12/23  2011/03/29  2012/01/30  2005/11/11  2016/12/23  2014/04/10
2     2006/06/16  2005/07/13  2007/06/27  2007/06/19  2005/07/13  2010/10/29  2015/09/29
3     2011/08/19  2010/04/12  2010/04/12  2012/08/21  2007/01/12  2015/12/18  2011/09/09
4     .  .  .  .  .  .  .
5     .  2016/01/21  .  .  .  .  .
;
run;

Re: Calculate difference in days between two dates for all possible pairwise date combinations

Solsidan — Tue, 28 May 2019 17:58:11 GMT

Thank you PaigeMiller, its exactly what I was looking for.