BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
couthelle
Fluorite | Level 6

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!

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

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;

 

The Boston Area SAS Users Group (BASUG) is hosting an in person Meeting & Training on June 27!
Full details and registration info at https://www.basug.org/events.

View solution in original post

7 REPLIES 7
Tom
Super User Tom
Super User

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
PROC Star

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
Super User

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;

 

The Boston Area SAS Users Group (BASUG) is hosting an in person Meeting & Training on June 27!
Full details and registration info at https://www.basug.org/events.
couthelle
Fluorite | Level 6

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
Super User
Macro variables don’t resolve inside single quotes so you need to use double quotes, eg:

where a.snap_dt<=“&max_dt”d;
The Boston Area SAS Users Group (BASUG) is hosting an in person Meeting & Training on June 27!
Full details and registration info at https://www.basug.org/events.
Tom
Super User Tom
Super User

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
Super User

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.

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 2335 views
  • 6 likes
  • 5 in conversation