Macro date variable inside macro loop with %if condition

[ChristianWI]

I have a table of "end dates" that I want to loop over "j" times inside a macro that includes an %if date = INTNX('month',end_date,&j) condition. For some reason the %if condition for j=0 is not evaluated as "true" even though the condition appears to hold if I test it within a separate data step.

data have;
format date_last date9.;
input date_last date9.;
datalines;
01JUL2022
run;

proc sql;
select date_last into :date_last
from have
;
quit;

%macro want;
%do j = 0 %to 0;
%let date = %sysfunc(intnx(month,"&date_last"d,&j.,b));

	data test;
	date = &date.;
	date_last = "&date_last"d;
	if &date. = "&date_last"d then test = 1;
	run;

	%if &date. = "&date_last"d %then %do;
		data want;
		test = 1;
		run;
	%end;
%end;
%mend;
%want; 

Any ideas?

[andreas_lds]

Remove all macro-code and write it down with normal sas code for at least one case. Than post that code and we see how to generalize it. Moving data into macro variables leads almost always to unnecessary complex code.

[PaigeMiller · Accepted solution]

if &date. = "&date_last"d then test = 1;

This compares the text string &date (which has value 22827) to the text string "&date_last"d which has value "01JUL2022"d. These text strings do not match.

You do not want formatted value of macro variables when the values are dates. (See Maxim 28) You can fix this by un-formatting &date_last (done in the DATA step that creates HAVE) and replacing "&date_last"D with &date_last

data have;
/* format date_last date9.; */
input date_last date9.;
datalines;
01JUL2022
run;

proc sql;
select date_last format=7. into :date_last
from have
;
quit;
%put &=date_last;

%macro want;
%do j = 0 %to 0;
%let date = %sysfunc(intnx(month,&date_last,&j.,b));
%put &=date;
	data test;
	date = &date.;
	date_last = &date_last;
	if &date. = &date_last then test = 1;
	run;

%put &date=&date_last;
	%if &date. = &date_last %then %do;
		data want;
		test = 1;
		run;
	%end;
%end;
%mend;
%want

[Tom]

The normal %EVAL() macro function used by the %IF does not understand date literals.

Use %SYSEVALF() if you need to compare date literals.

%if %sysevalf(&date. = "&date_last"d ) %then %do;

Or put the UNFORMATTED value of the variable DATE_LAST into the macro variable DATE_LAST and then you won't need to use date literals.  So change the SQL query to:

proc sql noprint
select date_last format=32. into :date_last trimmed
from have
;
quit;

And then change all of the "&date_last"d to just &date_last .

[ChristianWI]

This worked too. Awesome thanks.