topic Re: How to compare dates across subjects in SAS Programming

How to compare dates across subjects

yawenyu — Wed, 14 Mar 2018 20:15:03 GMT

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

Re: How to compare dates across subjects

Reeza — Wed, 14 Mar 2018 17:53:10 GMT

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

Re: How to compare dates across subjects

Reeza — Wed, 14 Mar 2018 17:54:00 GMT

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

Re: How to compare dates across subjects

ballardw — Wed, 14 Mar 2018 18:02:52 GMT

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.