I have the below code that seems not to work the error seems to the "FROM" in my macro EXTRACT (Year FROM <DATE>) see the error below
75 EXTRACT(MONTH FROM ADD_MONTHS(CURRENT_DATE,0)) ---- 22 202 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, ), *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=. ERROR 202-322: The option or parameter is not recognized and will be ignored
This is the code
rsubmit; %macro monmac (month=,tablename=, year=); proc sql; create table homedir.Table2 as (select subproduct, travel_year, travel_month, brx_anclry_rev_vlu FROM Homedir.Anc_Flown_All WHERE travel_year = &year. AND travel_month = &month.); quit; %mend monmac; endrsubmit; rsubmit; %monmac(tablename = Forecast1, month = EXTRACT(MONTH FROM ADD_MONTHS(CURRENT_DATE,0)),year = EXTRACT(YEAR FROM ADD_MONTHS(CURRENT_DATE,0))) run; endrsubmit;
What did you think this expression was going to do?
EXTRACT(MONTH FROM ADD_MONTHS(CURRENT_DATE,0))
Looks like you might have meant to code something like this instead:
MONTH(CURRENT_DATE)
Or possibly
MONTH(datepart(CURRENT_DATE))
Doesn't really have anything to do with macro variable or macro processing.
There is not EXTRACT() function that is supported by PROC SQL.
What are you trying to do?
And where do you get this extract() function and syntax from? Is it MySql or Oracle or something? SAS proc sql only supports ANSI SQL and SAS functions. What I think you want is something like:
%monmac (month=,tablename=,year=);
proc sql; create table homedir.Table2 as select subproduct, travel_year, travel_month, brx_anclry_rev_vlu from homedir.anc_flown_all where travel_year=&year. and travel_month=&month.; quit;
%mend monmac;
%monmac (tablename=forecast1,month=%str(month(intnx('months',today(),0))),year=%str(year(intnx('year',today(),0))));
Although I can't see why you would want to do all that in the first place as the macro part adds nothing to the code at all, you can re-write like this, which is far simpler:
%let inc=0;
proc sql; create table homedir.Table2 as select subproduct, travel_year, travel_month, brx_anclry_rev_vlu from homedir.anc_flown_all where travel_year=intnx('year',today(),&inc.) and travel_month=intnx('month',today(),&inc.); quit;
Hi thanks for replying i got the Extract from terdata which is where i coded the SQL, so is the problem that I am using TERADATA functions in SAS?
What did you think this expression was going to do?
EXTRACT(MONTH FROM ADD_MONTHS(CURRENT_DATE,0))
Looks like you might have meant to code something like this instead:
MONTH(CURRENT_DATE)
Or possibly
MONTH(datepart(CURRENT_DATE))
Thanks tom i see the error I made I was using Teradata syntax in SAS thanks
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.