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
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.