BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
elwayfan446
Barite | Level 11

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?

1 ACCEPTED SOLUTION

Accepted Solutions
elwayfan446
Barite | Level 11

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;

View solution in original post

21 REPLIES 21
Kurt_Bremser
Super User

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
Barite | Level 11

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

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

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
elwayfan446
Barite | Level 11

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

PaigeMiller
Diamond | Level 26

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)

--
Paige Miller
mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
AMSAS
SAS Super FREQ

@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
Barite | Level 11

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

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
Barite | Level 11

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

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
Diamond | Level 26

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;
--
Paige Miller
elwayfan446
Barite | Level 11

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 21 replies
  • 1719 views
  • 2 likes
  • 5 in conversation