Calling Macro Generating Syntax Error

[elwayfan446]

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 &current_date_file_created;

Log file generates the datetime value of the macro

24
25 %put &current_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
&current_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?

[Kurt_Bremser]

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.

[elwayfan446]

@Kurt_Bremser I have tried it as a datetime constant as well and get the following error.

, '&current_date_file_created'dt as DATE_FILE_CREATED format=datetime21.2
ERROR: Invalid date/time/datetime constant '&current_date_file_created'dt.

[Kurt_Bremser]

---

@elwayfan446 wrote:

@Kurt_Bremser I have tried it as a datetime constant as well and get the following error.

, '&current_date_file_created'dt as DATE_FILE_CREATED format=datetime21.2
ERROR: Invalid date/time/datetime constant '&current_date_file_created'dt.

---

Macro variables are not resolved when enclosed with single quotes; use double quotes.

[mkeintz]

---

@elwayfan446 wrote:

Log file generates the datetime value of the macro

24
25 %put &current_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
&current_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 &current_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 "&current_date_file_created"n)?  I doubt it.  What are you trying to accomplish?  I.e. what do you want dataset DATE to contain?

[elwayfan446]

@mkeintz  I want that date to be the data pulled as the date_file_created in the proc sql; query.

[PaigeMiller]

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)

[mkeintz]

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.

[AMSAS]

@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

[elwayfan446 · Accepted solution]

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
, &current_date_file_created as date_file_created
from table1
;
quit;

[Kurt_Bremser]

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
"&current_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
&current_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;

[elwayfan446]

@Kurt_Bremser 

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
&current_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
"&current_date_file_created"dt as date_file_created format=datetime21.2
from table1
;
quit;

[Kurt_Bremser]

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;

[PaigeMiller]

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
&current_date_file_created /* NO QUOTES NEEDED HERE, NO dt NEEDED ON THE END */
as date_file_created format=datetime21.2
from table1
;
quit;

[elwayfan446]

@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?