Hello.
I am having trouble trying to figure out why an error is generating when I am calling a macro that I created. Here is what I am doing:
proc sql;
select max(export_created_date_time) format=datetime21.2
into :current_date_file_created
from msrflow.msrflow_dlytemp
;
quit;
%put ¤t_date_file_created;
Log file generates the datetime value of the macro
24
25 %put ¤t_date_file_created;
03AUG2020:15:38:26.00
26
I am calling that macro in a simple query
proc sql;
create table data as
select
¤t_date_file_created as date_file_created format=datetime21.2
from table1
;
quit;
I get the following syntax error:
NOTE: Line generated by the macro variable "CURRENT_DATE_FILE_CREATED".
106 03AUG2020:15:38:26.00
_______
22
76
3 The SAS System 09:28 Tuesday, August 4, 2020
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?,
AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH,
LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.
ERROR 76-322: Syntax error, statement will be ignored.
Any tips on what I am doing wrong?
I have written the query without a variable and it works fine.
proc sql;
create table data as
select
ID
, exported_date as date_file_created
from table1
;
quit;
Results are:
ID date_file_created
12487 03AUG2020:15:38:26.00
I want the same results by calling the variable I have set.
proc sql;
create table data as
select
ID
, ¤t_date_file_created as date_file_created
from table1
;
quit;
After the macro processor resolves the macro variable, you end up with this code:
proc sql;
create table data as
select
03AUG2020:15:38:26.00 as date_file_created format=datetime21.2
from table1
;
quit;
Do you think this is valid syntax? If you think "no" (and SAS thinks "no"), look if you can find a way to make it valid.
Hint: see SAS Constants in Expressions, and Maxim 28.
@Kurt_Bremser I have tried it as a datetime constant as well and get the following error.
, '¤t_date_file_created'dt as DATE_FILE_CREATED format=datetime21.2
ERROR: Invalid date/time/datetime constant '¤t_date_file_created'dt.
@elwayfan446 wrote:
@Kurt_Bremser I have tried it as a datetime constant as well and get the following error.
, '¤t_date_file_created'dt as DATE_FILE_CREATED format=datetime21.2 ERROR: Invalid date/time/datetime constant '¤t_date_file_created'dt.
Macro variables are not resolved when enclosed with single quotes; use double quotes.
@elwayfan446 wrote:
Log file generates the datetime value of the macro
24
25 %put ¤t_date_file_created;
03AUG2020:15:38:26.00
26
I am calling that macro in a simple query
proc sql; create table data as select ¤t_date_file_created as date_file_created format=datetime21.2 from table1 ; quit;
I get the following syntax error:
NOTE: Line generated by the macro variable "CURRENT_DATE_FILE_CREATED". 106 03AUG2020:15:38:26.00 _______ 22 76 3 The SAS System 09:28 Tuesday, August 4, 2020 ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=. ERROR 76-322: Syntax error, statement will be ignored.
Any tips on what I am doing wrong?
Using the value you report for the macrovar ¤t_date_file_created
generates this code in you "create table data" statement:
proc sql;
create table data as
select 03AUG2020:15:38:26.00 as date_file_created format=datetime21.2
from table1;
quit;
Do you really have a variable named 03AUG2020:15:38:26.00 (in which case the syntax should be "¤t_date_file_created"n)? I doubt it. What are you trying to accomplish? I.e. what do you want dataset DATE to contain?
@mkeintz I want that date to be the data pulled as the date_file_created in the proc sql; query.
So first write code that works without macro variables. Write code that works using the date/time 03AUG2020:15:38:26.00 hard coded. Once you have that working, then you should be able to create a macro.
Also, just because you are using a macro variable does not mean you can ignore or avoid the syntax rules of whatever PROC you are using (in this case PROC SQL)
Are you trying to filter observations from table1, based on some datetime variable having the value "03AUG2020:15:38:26.00"dt?
If so then you might want
create mytable as select * from table1
where some_variable="03AUG2020:15:38:26.00"dt ;
You will need to explain you purpose more clearly.
@elwayfan446
The point the other 2 replies and SAS error message is attempting to make is 03AUG2020:15:38:26.00 is not a valid SAS variable name.
I would recommend that you write the PROC SQL step first without the macro variable and get that working, then add the macro variable into the mix
I have written the query without a variable and it works fine.
proc sql;
create table data as
select
ID
, exported_date as date_file_created
from table1
;
quit;
Results are:
ID date_file_created
12487 03AUG2020:15:38:26.00
I want the same results by calling the variable I have set.
proc sql;
create table data as
select
ID
, ¤t_date_file_created as date_file_created
from table1
;
quit;
You have several options:
1. correct use of a datetime literal
proc sql;
select max(export_created_date_time) format=datetime21.2
into :current_date_file_created
from msrflow.msrflow_dlytemp
;
create table data as
select
"¤t_date_file_created"dt as date_file_created format=datetime21.2
from table1
;
quit;
2. not using a format at all (Maxim 28!)
proc sql;
select max(export_created_date_time)
into :current_date_file_created
from msrflow.msrflow_dlytemp
;
create table data as
select
¤t_date_file_created as date_file_created format=datetime21.2
from table1
;
quit;
3. not using a macro variable at all
proc sql;
create table data as
select
(select
max(export_created_date_time)
from msrflow.msrflow_dlytemp
) as date_file_created format=datetime21.2
from table1
;
quit;
In my original post, this option is what I did and it gave me the error.
2. not using a format at all (Maxim 28!)
proc sql;
select max(export_created_date_time)
into :current_date_file_created
from msrflow.msrflow_dlytemp
;
create table data as
select
¤t_date_file_created as date_file_created format=datetime21.2
from table1
;
quit;
However, I tried your option 1 and got the expected results!
proc sql;
select max(export_created_date_time) format=datetime21.2
into :current_date_file_created
from msrflow.msrflow_dlytemp
;
create table data as
select
"¤t_date_file_created"dt as date_file_created format=datetime21.2
from table1
;
quit;
Please post the whole log of your SQL steps where you received a syntax error. I am asking because this works:
data datetimes;
input dt datetime19.;
datalines;
03aug2020:18:43:00
;
proc sql noprint;
select max(dt) into :max_dt from datetimes;
create table want as select &max_dt. as max_dt format=datetime19.
from sashelp.class;
quit;
DO NOT FORMAT MACRO VARIABLES!!!!! Modifying your code
proc sql;
select max(export_created_date_time) /*format=datetime21.2*/ /* DO NOT FORMAT MACRO VARIABLES */
into :current_date_file_created
from msrflow.msrflow_dlytemp
;
create table data as
select
¤t_date_file_created /* NO QUOTES NEEDED HERE, NO dt NEEDED ON THE END */
as date_file_created format=datetime21.2
from table1
;
quit;
@PaigeMiller no quotes was giving me the error. Double quotes worked as noted in an answer above.
Instead of hitting me with the caps lock, can you explain why variables shouldn't be formatted so I (and others who find this thread) will know in the future?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.