How to compare dates across subjects

[yawenyu]

Hello SAS Community,

I am attempting to compare dates for reporting purposes. I am using SAS 9.3. For this case, I have a list of subjects with dates (date9. format) like so:

Name      CompareDate      Date1               Date2                Date3

Bob          09-04-1993          06-21-1992      10-23-1990        01-21-1991

Alice         01-30-2014         05-07-2003       02-02-2015        05-15-2016

Sam         10-05-1989          04-17-1982       12-23-1988       09-07-1985

Mark                                     02-22-2013       06-01-1997       04-02-2001

Adam        05-23-1996         10-14-1992       03-15-1994       02-16-1991

Dan           11-12-2005         07-06-2002       04-23-2000       12-24-2007

Sandy       03-07-2000          12-04-1993       08-09-1994       04-01-1996

I would like to compare Date1-Date3 with CompareDate, and output any subjects with related dates that occur after it. Note that Mark does not have any value for CompareDate - in this case, he would be ignored in this comparison. Otherwise, it would Alice with 02-02-2015 and 05-15-2016, and Dan with 12-24-2007. The output would be something like:

Name       Compare Date        Date                 VarName

Alice         01-30-2014              02-02-2015       Date2

Alice         01-30-2014              05-15-2016       Date3

Dan          11-12-2005              12-24-2007       Date3

To start, I created a macro variable with CompareDate but I am not sure if it will work across multiple subjects. Can someone please point me in the right direction? Please let me know if you need further clarification.

Thank you in advance,

Yawen

[Reeza]

I would like to compare Date1-Date3 with CompareDate, and output any subjects with related dates that occur after it. 

is equivalent to:  take the maximum date and checking if it's after the compare date.

Which in code is:

if max(of date-date3) > compareDate and not missing(compareDate);

---

@yawenyu wrote:

Hello SAS Community,

I am attempting to compare dates for reporting purposes. I am using SAS 9.3. For this case, I have a list of subjects with dates (date9. format) like so:

Name      CompareDate      Date1               Date2                Date3

Bob          09-04-1993          06-21-1992      10-23-1990        01-21-1991

Alice         01-30-2014         05-07-2003       02-02-2015        05-15-2011

Sam         10-05-1989          04-17-1982       12-23-1988       09-07-1985

Mark                                     02-22-2013       06-01-1997       04-02-2001

Adam        05-23-1996         10-14-1992       03-15-1994       02-16-1991

Dan           11-12-2005         07-06-2002       04-23-2000       12-24-2007

Sandy       03-07-2000          12-04-1993       08-09-1994       04-01-1996

I would like to compare Date1-Date3 with CompareDate, and output any subjects with related dates that occur after it. Note that Mark does not have any value for CompareDate - in this case, he would be ignored in this comparison. Otherwise, it would Alice with 02-02-2015 and Dan with 12-24-2007. The output would be something like:

Name       Compare Date        Date                 VarName

Alice         01-30-2014              02-02-2015       Date2

Dan          11-12-2005              12-24-2007       Date3

To start, I created a macro variable with CompareDate but I am not sure if it will work across multiple subjects. Can someone please point me in the right direction? Please let me know if you need further clarification.

Thank you in advance,

Yawen

---

[Reeza]

You can use WHICHN to find out which date is actually larger than the comparison, what happens if multiple dates are greater?

[ballardw]

First thing is to confirm that your values are SAS date valued numeric with a date format (what you show is NOT date9. it is mmddyyD10.).

Comparisons of dates then become much easier.

If that is the case then something like this may get you started:

data want; 
   set have;
   array dd date1-date3;
   length varname $ 10;
   do i= 1 to dim(dd);
      if dd[i] > comparedate> 0 then do;
         date = dd[i];
         varname= vname(dd[i]);
         output;
      end;
   end;
   keep name comparedate date varname;
run;

The comparedate>0 portion of the if statement is exclude those records with missing comparedate. This does assume that none of your comparedates are prior to 1 Jan 1960. If that might happen you could replace 0 with something like '01JAN1900'd or other literal date before your actual data.