topic Re: Calling Macro Generating Syntax Error in SAS Programming

Calling Macro Generating Syntax Error

elwayfan446 — Tue, 04 Aug 2020 16:08:49 GMT

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?

Re: Calling Macro Generating Syntax Error

Kurt_Bremser — Tue, 04 Aug 2020 16:17:47 GMT

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.

Re: Calling Macro Generating Syntax Error

mkeintz — Tue, 04 Aug 2020 16:21:08 GMT

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

Re: Calling Macro Generating Syntax Error

elwayfan446 — Tue, 04 Aug 2020 16:30:03 GMT

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

Re: Calling Macro Generating Syntax Error

PaigeMiller — Tue, 04 Aug 2020 16:34:37 GMT

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)

Re: Calling Macro Generating Syntax Error

elwayfan446 — Tue, 04 Aug 2020 16:34:58 GMT

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

Re: Calling Macro Generating Syntax Error

mkeintz — Tue, 04 Aug 2020 16:35:56 GMT

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.

Re: Calling Macro Generating Syntax Error

AMSAS — Tue, 04 Aug 2020 16:36:17 GMT

@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

Re: Calling Macro Generating Syntax Error

Kurt_Bremser — Tue, 04 Aug 2020 16:38:59 GMT

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;

Re: Calling Macro Generating Syntax Error

Kurt_Bremser — Tue, 04 Aug 2020 16:39:53 GMT

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

Re: Calling Macro Generating Syntax Error

elwayfan446 — Tue, 04 Aug 2020 16:41:22 GMT

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;

Re: Calling Macro Generating Syntax Error

elwayfan446 — Tue, 04 Aug 2020 16:45:51 GMT

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

Re: Calling Macro Generating Syntax Error

Kurt_Bremser — Tue, 04 Aug 2020 16:52:09 GMT

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;

Re: Calling Macro Generating Syntax Error

PaigeMiller — Tue, 04 Aug 2020 16:55:51 GMT

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;

Re: Calling Macro Generating Syntax Error

elwayfan446 — Tue, 04 Aug 2020 18:09:42 GMT

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

Re: Calling Macro Generating Syntax Error

PaigeMiller — Tue, 04 Aug 2020 18:38:39 GMT

@elwayfan446 wrote:

@PaigeMiller no quotes was giving me the error. Double quotes worked as noted in an answer above.

No quotes and no formatting of the macro variables works fine.

Formatting macro variables is not necessary for logic or arithmetic. SAS does all internal logic (such as comparing dates or date/times) on the unformatted values anway. So, to take a simple example, you can create a macro variable &datevar that is formatted as 01JAN2020, and then today()>"&datevar"d is the exact same thing as if the macro variable is unformatted and has value 21915 and then you use today()>&datevar. So there's extra programming work formatting the variable and then you have to un-format it by putting quotes around it and appending a d (for date values) or dt (for date/time values). I recommend you just avoid that extra work and use unformatted macro variables.

Re: Calling Macro Generating Syntax Error

Kurt_Bremser — Wed, 05 Aug 2020 07:51:41 GMT

@elwayfan446 wrote:

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

@PaigeMiller hits you with capslock to get your attention, as you do not seem to actually run the codes we supply for proof.

Once again, see my Maxim 28 (look at my footnotes for the link) for a short explanation.

Keep in mind that resolving macro variables (and macros) only replaces text and does nothing else. Wherever a raw value in code would be sufficient, use it in the macro variable, so you don't have to constantly think about correctly de-formatting a value when you reference the macrovar.

%let mvar = %sysfunc(today());

%put &=mvar;

data _null_;
if &mvar = today()
then put "Yes!";
else put "No!";
run;

Re: Calling Macro Generating Syntax Error

elwayfan446 — Wed, 05 Aug 2020 13:34:12 GMT

Perfect. Thank you for the explanation. I had always done it in the macro with no issues but will move away from that going forward.

Re: Calling Macro Generating Syntax Error

Kurt_Bremser — Wed, 05 Aug 2020 13:52:36 GMT

The only time I use formatted dates or times in macro variables is when I get them from user prompts. And if I use them repeatedly in the code, I de-format them once at the beginning.

Re: Calling Macro Generating Syntax Error

mkeintz — Wed, 05 Aug 2020 14:39:48 GMT

@Kurt_Bremser wrote:

The only time I use formatted dates or times in macro variables is when I get them from user prompts. And if I use them repeatedly in the code, I de-format them once at the beginning.

@Kurt_Bremser :

I think we part ways a bit on this one. In the case of date, time, or datetime values for macrovars, I typically keep them in the form they would otherwise have as literals, i.e.

%let reference_date=06jun2019;
or
%let start_time= 23:01:35.356;
or
%let begin_point= &reference_date.:&start_time;
or
%let begin_point=06jun2019:23:01:35.356;

These expressions are easy for the user to read in any macro-generated code, yet they are also easy to use because they are ready-formatted for use in date, time, or datetime literals, as in:

data want;
  set have;
   where event_date>="&reference_date"d;
   where evt_time>"&start_time"t;
   where event_dt>="&begin_point"dt:
run;

To me this is one of the primary benefits of the macro processor being allowed to resolve macro expressions in double quotes, while being prohibited from the same when in single quotes.