Search code examples
sassas-macro

Comparing dates in sas macro variables

I'm trying to compare dates. The if statements in the code below don't resolve as expected. Why is that? (The run date should be greater than the start date but less than the end date)

%let start_date = %sysfunc(intnx(day,%sysfunc(today()),-7),mmddyy8.);
%let end_date = %sysfunc(intnx(day,%sysfunc(today()),-2),mmddyy8.);
%let run_date = %sysfunc(intnx(day,%sysfunc(today()),-5),mmddyy8.);

%put &start_date &end_date &run_date;

%if &run_date > &start_date %then %do; %put success; %end;
%if &run_date < &end_date %then %do; %put success; %end;
Solution

  • You are putting strings like 06/07/23 and 06/14/23 into your macro variables. Those are not going to compare in the same order as dates would. (Why didn't you include the CENTURY in your date strings?)

    Also the %IF statement has an implied %EVAL() call. So 06/07/23 will be treated as a request to do division and not a string.

    Just use the actual values instead of the formatted values. Then they will order properly.

    %let start_date = %sysfunc(intnx(day,%sysfunc(today()),-7));
%let end_date = %sysfunc(intnx(day,%sysfunc(today()),-2));
%let run_date = %sysfunc(intnx(day,%sysfunc(today()),-5));

    If you need the strings to be recognizable to humans and be able to order them then use the YYMMDD or YYMMDDN format. Then the lexigraphical ordering is the same as the chronological ordering. Make sure to leave room for four digit years.

    %let start_date = %sysfunc(intnx(day,%sysfunc(today()),-7),yymmddn8.);
%let end_date = %sysfunc(intnx(day,%sysfunc(today()),-2),yymmddn8.);
%let run_date = %sysfunc(intnx(day,%sysfunc(today()),-5),yymmddn8.);

    A way to have strings that humans recognize and SAS also understands is to use DATE literals. Those require a string that the DATE informat can recognize. So use the DATE format to generate them and add the quotes and letter D suffix.

    %let start_date = "%sysfunc(intnx(day,%sysfunc(today()),-7),date9.)"d;

    But the %EVAL() function does not recognize date literals as dates so add %SYSEVALF() to evaluate your inequality.

    %if %sysevalf(&run_date > &start_date) %then %do; %put success; %end;