Let macro variable be max date in dataset

[couthelle]

Hello,

I bumped into this post % Let macro variable be max date in dataset. 

I followed the solution but when I created and called the macro, I'm getting this error message:

35         21AUG2023
             _______
             22
             76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, GE, GET, 
              GROUP, GT, GTT, HAVING, LE, LET, LT, LTT, NE, NET, OR, ORDER, ^=, |, ||, ~=.  

ERROR 76-322: Syntax error, statement will be ignored.

This is my code :

proc sql;
select max(a.payout_dt) format=date9.
	into :max_dt
	from my.table a;
quit;

proc sql;
create table report as 
	select *
	from my.database a
	where a.snap_dt<=&max_dt;
quit;

Appreciate any feedback that can help me troubleshoot this.

Thanks!

[Tom]

You generated this code:

where a.snap_dt<=21AUG2023

which is clearly not valid SAS code.

You need to either generate code like one of these two options

where a.snap_dt<="21AUG2023"d
where a.snap_dt<=23243

So you could modify the WHERE clause like this:

where a.snap_dt<="&max_dt"d

Or leave the where clause as it is and create the macro variable without the formatting.

select max(a.payout_dt) format=32. into :max_dt trimmed from my.table a;

[Astounding]

When you refer to the macro variable here:

where a.snap_dt<=&max_dt;

it resolves as you would expect:

where a.snap_dt<=21AUG2023;

 However, that's not the correct syntax to refer to a date in SAS language (whether or not macro language is involved).  Just as a matter of correct SAS syntax, you would need to generate:

where a.snap_dt<="21AUG2023"D;

 Therefore, your macro language reference needs to add the quotes and the D :

where a.snap_dt<="&max_dt"D;

[Quentin · Accepted solution]

Remember that macro variables are about code substitution.

If you run:

data mytable ;
  payout_dt='21Aug2023'd ;
run ;

proc sql;
select max(a.payout_dt) format=date9.
	into :max_dt
	from mytable a;
quit;

%put &=max_dt ;

You'll see that the macro variable MAX_DT has the value: 21AUG2023

When you code:

proc sql;
create table report as 
	select *
	from my.database a
	where a.snap_dt<=&max_dt;
quit;

It is the same as coding:

proc sql;
create table report as 
	select *
	from my.database a
	where a.snap_dt<=21Aug2023;
quit;

which will error, because 21Aug2023 is not  valid syntax.  It's not the name of a variable, and it's not a date value.  If you want to a date value, you can use a SAS date literal value:

proc sql;
create table report as 
	select *
	from my.database a
	where a.snap_dt<="21Aug2023"d;
quit;

So with a macro variable, you can code it like:

proc sql;
create table report as 
	select *
	from my.database a
	where a.snap_dt<="&max_dt"d;
quit;

Note this is only needed because you specified that the value in the macro variable should be formatted as date9.  If you remove the format option in the  PROC SQL step that creates the macro variable, your code would look like:

proc sql;
select max(a.payout_dt)
	into :max_dt
	from mytable a;
quit;

In that case, your macro variable MAX_DT would have the value 23243.  And that value would work in your original code because 23243 is a valid date value.  Meaning, you could code:

proc sql;
create table report as 
	select *
	from my.database a
	where a.snap_dt<=23243;
quit;

[couthelle]

I really appreciate the explanation.

I did fix my code to this

proc sql;
select max(a.payout_dt) format=date9.
	into :max_dt
	from my.table a;
quit;

proc sql;
create table report as 
	select *
	from my.database a
	where a.snap_dt<='&max_dt'd;
quit;

but I'm got a new error

48         where a.snap_dt<='&max_dt'd;
ERROR: Invalid date/time/datetime constant '&max_dt'd.

FYI, the the variables properties are:

• a.payout_dt Format Date9., Informat Date9.
• snap_dt format Date9.

That's why not sure why I'm now getting this error when I already formatted max_dt as date9.

Thanks for your help again!

[Quentin]

Macro variables don’t resolve inside single quotes so you need to use double quotes, eg:

where a.snap_dt<=“&max_dt”d;

[Tom]

Use double quotes when you want the macro processor to take action on a quoted string.

The value inside the quotes in a date literal has to be something the DATE informat can understand.

Since you used single quote characters instead of double quote characters the macro processor ignored the &max_dt inside the single quotes.  So the DATE informat did not know what to do with the literal characters &max_dt you passed it.

[ballardw]

For values to be compared don't use formatted values.

Would you typically write code that looked like?

where var = 01Jan2023;

That would incorrect for either a character variable or date value.

I bet this works if snap_dt is actually a date value.

proc sql;
select max(a.payout_dt) 
	into :max_dt
	from my.table a;
quit;

proc sql;
create table report as 
	select *
	from my.database a
	where a.snap_dt<=&max_dt;
quit;

If you apply a format to the macro variable then you need to create a date literal value. With &max_dt

that would look like "&max_dt."d for SAS to recognize the use as a date.