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

Why are you using the macro variables to generate strings instead of date values? — Tom, Nov 04 '22 at 17:42

score 1 · Accepted Answer · answered Nov 04 '22 at 16:38

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

Tom · Answer 2 · 2022-11-04T19:40:12.567

Note that in a PROC SQL query the format attached to a variable does not carry over to the result of aggregate functions, like MIN() and MAX(), performed on the variable. For numeric variables PROC SQL will use the BEST8. format when converting the number into a string to store into the macro variable. You can remove the extra spaces that causes by adding the TRIMMED keyword.

proc sql noprint;
select min(startdate), max(enddate)
  into :start_date trimmed
     , :end_date trimmed
from work.mydata
;
quit;

Do not add quotes around the values generated by expanding the macro variables. That would generate a string literal and not a numeric literal.

where date between &start_date and &end_date

If you want the values put into the macro variables by the into syntax to be formatted in some other way you need to attach the format as part of the query.

For example if you wanted the value to be something that could be used to generate a date literal, that is a string that the DATE informat understands, then use the DATE format. Make sure the width used is long enough to include all four digits of the year.

proc sql noprint;
select min(startdate) format=date9.
     , max(enddate) format=date9.
  into :start_date trimmed
     , :end_date trimmed
from work.mydata
;
quit;
...
where date between "&start_date"d and "&end_date"d

Macro variable (date) not working as expected in query

2 Answers2