Macro variable (date) not working as expected in query

Question

I've several SAS (PROC SQL) queries using a MIN(startdate) and MAX(enddate).
To avoid having to calculate these every time I want to do this once at the beginning and store it in a macro variable but I get an error every time.

What is going wrong or how to achieve this ?
Thanks in advance for the help !

This works:

WHERE DATE BETWEEN 
    (SELECT MIN(startdate format yymmddn8. FROM work.mydata)
AND (SELECT MAX(enddate format yymmddn8. FROM work.mydata)

DATE format is YYMMDD8n and length is 8.

Creating macro variables:

PROC SQL;
    SELECT MIN(startdate), MAX(enddate)
    INTO :start_date, :end_date
    FROM work.mydata
QUIT;

/*Formatting the macro variable:*/
%macro format(value,format);
    %if %datatyp(&value)=CHAR
        %THEN %SYSFUNC(PUTC(&value, &format));
    %ELSE %LEFT(%QSYSFUNC(PUTN($value,&format)));
%MEND format;

Tried:

WHERE DATE BETWEEN "%format(&start_date, yymmddn8.)" AND "%format(&end_date, yymmddn8.)"   

Error message:

ERROR: Expression using equals (=) has components that are of different data types

Answer 1

First, you are missing d when providing date for BETWEEN operator.

WHERE DATE BETWEEN "%format(&start_date, yymmddn8.)"d AND "%format(&end_date, yymmddn8.)"d

But keep in mind tht date string must be in date9. format.

"4NOV2022"d

Second, you dont need to format date for this WHERE condition. Date is numeric and numeric value whould work fine.

WHERE DATE BETWEEN &start_date AND &end_date

If you really want to have date formated you can format it directly inside PROC SQL:

PROC SQL;
    SELECT
        MIN(startdate) format=date9.,
        MAX(enddate) format=date9.
    INTO
        :start_date,
        :end_date
    FROM
        work.mydata
QUIT;

and then

WHERE DATE BETWEEN "&start_date"d AND "&end_date"d