Date logic error

[cho16]

Hi ..

When I try to use the code below, I receive the error message listed below. Kindly help me with this.

%let date_cntrl = %sysfunc(intnx(day, %sysfunc(today()), -7), mmddyy10.);
%put &date_cntrl.;

data week;
set Stat_look;
where date <= '&date_cntrl.'d;
run;

Getting an below error:

[PaigeMiller · Accepted solution]

Use double quotes

where date <= "&date_cntrl"d;

Or even easier, use Maxim 28, do not format macro variables.

%let date_cntrl = %sysfunc(intnx(day, %sysfunc(today()), -7));
%put &=date_cntrl;

data week;
    set Stat_look;
    where date <= &date_cntrl;
run;

[ballardw]

Simple in this case. Two errors: First macro variables inside single quotes do not resolve. Use "  " when needed.

After you fix that then the date value must be a DATE9 (or 7 or similar), i.e. 14JUL2022 format, not MMDDYY format that you applied.

Yet another case of formatting a date when not needed. If you want the value to compare then don't format a macro variable. About the only valid reason to do so is when the value is used in something people read like Title text or the name of a file. If the only use of the variable is comparison as shown then don't format at all and just use the numeric value. If you are not using the macro variable a many different places it may be better to just use the Intnx function in the data step instead of a macro variable.

%let date_cntrl = %sysfunc(intnx(day, %sysfunc(today()), -7));
%put &date_cntrl.;

data week;
set Stat_look;
where date <= &date_cntrl.
run;

 

[PaigeMiller]

Good point, format date9. would work but format mmddyy10. will not work, even after you fix the single-quotes should be double-quotes error.

[Kurt_Bremser]

Apart from using double quotes, you need the DATE9. format for date literals, not MMDDYY10.

But it is much simpler to use unformatted values, as Maxim 28 says.