"Extract From date" Breaking my Macro variable

[lloydsanford]

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; 

[Tom]

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?  

[RW9]

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;

[lloydsanford]

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?

[Tom · Accepted solution]

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))

[lloydsanford]

Thanks tom i see the error I made I was using Teradata syntax in SAS thanks